Deleting Entire Rows Containing A Specific Value
Aug 29, 2007
I've been tasked with developing a macro that will generate schedules. For background, we're taking a QuickBooks Customer file and exporting it to be picked up by the macro. I've got that part working, but now I need to delete various rows based on certain criteria.
The spreadsheet has a field for Job Status which we use to define whether an account is under contract (2 in QuickBooks), or some other status. Basically what I need to do is delete the entire row of data where JobStatus (Column R) is anything but 2.
View 9 Replies
ADVERTISEMENT
Nov 24, 2011
I've got a report which is approx 40,000 rows long and I need to delete out alot of the info.
I need to delete out entire rows if column C contains a 0.
I've written a loop to do this but as theres so much data it take a long time to run.
Is there a smarter way to do this?
View 6 Replies
View Related
Dec 17, 2007
I have a long list (over 1000 lines) of numbers, some of which are duplicated. Up to now I have been sorting them, adding in a simple check column to see if the number is a duplicate of the one below, and then manually deleting this cell.
Is there a better way to do this using VBA? The only way I have managed to find is by deleting the entire row, but I cannot do this as I have additional data to the right that I need to keep.
I also cannot install any add-ons as this on a work computer.
View 9 Replies
View Related
Feb 21, 2009
Here's my problem. I have the following table:
View 5 Replies
View Related
Nov 9, 2008
I have created a worksheet (through an import into MS Excel 2007) which contains 287,281 rows. However, the data I need is located in rows: 4, 67, 130, ... (or n+63) rows.
View 10 Replies
View Related
Mar 8, 2012
I want to delete some rows that are identical. Aside from creating a macros to do this, is there a way to find a row with a specific criteria and delete it if value returns TRUE?
View 1 Replies
View Related
Jul 31, 2013
I was wondering if there is a formula or command to find and delet specific rows. I want to remove people from an excel email list.
On Sheet 1, I have 500 rows with columns for first name, last name, and email.
On Sheet 2, I have 30 rows with columns for first name, last name, and email that appear on Sheet 1 but need to be removed.
Is there a way to do this without manually searching for each email and then deleting the row?
View 4 Replies
View Related
Nov 28, 2006
i need to create a macro that will delete entire rows based on certain criteria. here is an example of some of the worksheet:
US0017651060AMR_CORP CM 12C
US0017658669AMR_CORP PR 8C
US00211H1077ATC_ONLANE_INC CM 92M
US00211H2067ATC_ONLANE_INC PR 100N
US0038813079ACACIA_RESH CM 12C
US0038812089ACACIA_RESH CM 16C
CA00440P2017ACE_AVIATION CM 7C
CA00440P1027ACE_AVIATION CM 7C
US00440P3001ACE_AVIATION CM 100S
CA00756J1049ADVANTEX_MARK CM 43C
CA00756J3029ADVANTEX_MARK PR 100N
i want to create a macro that, when searching the third column of data (containing "CM" and "PR"), if a group of rows (between blank rows) do not contain at least one "PR", delete entire group of rows.
Therefore, the rows with "ACACIA_RESH" and "ACE_AVIATION" would be deleted from the worksheet.
View 9 Replies
View Related
Mar 25, 2014
I have been trying to find a way of formatting an entire row based on the contents of cells in each column. However i come unstuck when trying to make the column dynamic. Below is an example:
Month
2010
2011
2012
[Code]....
The idea is that i say current year is 2011 and all rows where C contains an a will turn green. What i would like to achieve is that when i change current year to say 2012 the conditional formatting adjusts so that it looks at D instead of C.
View 3 Replies
View Related
Sep 23, 2013
I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.
View 5 Replies
View Related
Feb 14, 2013
I have a complex report with thousands of lines I am trying to clean up. In order to do this, I need VBA that recognizes specific text data starting after row 9. When it sees this, it will need to select and delete the 2 rows above it, the row it is in, and the 6 rows below it. 9 rows in total, some of which are blank. The text will repeat every 47-50 rows, but I cannot say exactly when it will repeat again. When it is repeated, I need the 9 rows deleted. At this time, there is only 1 column in the report because I need to split out the data in the cell into adjoining cells. Since I have not solved that yet, I need to just delete the entire row.
The text is written with spaces between each letter and a double space between words. It reads, "B I L L I N G M A S T E R I N V O I C E S E T U P L I S T I N G"
View 8 Replies
View Related
Dec 24, 2007
I have a spreadsheet with thounsands of rows. Some of the rows I'd like to delete because thier values are below 5%. I wrote a VBA code, which primary function is to look for a cell and delete the row if the value is less than or equal to 5%. The code looks fine but I get an "Infinite Loop" anytime the first row value is not true. Below is the code,
-jungleman1
Sub Delete_Zero_Factors()
Dim i As Integer
Dim CUSIP As String
Sheets("Report").Select
Range("B5").Select
i = 0
While Not (IsEmpty(ActiveCell.Offset(i, 0).Value))
CUSIP = ActiveCell.Offset(i, 0).Value
Do While (ActiveCell.Offset(i, 1).Value
View 9 Replies
View Related
May 2, 2006
Need the code for deleting multiple columns simultaneously in an excel depending on some search criteria.Suppose I want to delete the columns whose names are "Roll No" ,"Age" & "Class"
View 2 Replies
View Related
Mar 16, 2013
I am trying to delete entire row that has certain text, let say "hi". I was able to delete cells with exact word "hi", but was not able to delete cells that have texts other than "hi". ex) "hi myname." Below is my vba code, and I keep getting run-time error and cannot execute the code.
VB:
Sub HiDelete()
Dim srchRng As Range
Set srchRng = ActiveSheet.Range("g:g")
For i = srchRng.Rows.Count To 1 Step -1
If worksheefunction.Search("hi", Cells(i, 7)) > 0 Then Rows(i).Delete
Next i
End Sub
View 3 Replies
View Related
Apr 11, 2012
Working on some code that will find the Column Name "CL" and delete the entire column. Here is what I have so far...it does not give me a error when it compiles, but for some reason the statement in red is being ignored. I tried switching Range with Columns, but that did not work.
Private sub coldelete ()
Dim Rng2 as Range
shUCLR = sheets("Unit Cost").usedrange.rows.count
Set Rng2 = Sheets("Unit Cost").Range("A1:BF1")
For Each d In Rng2
If d = "CL" Then
colnum = d.Column
colletter = Left(Sheets("Unit Cost").Cells(1, colnum).Address(False, False), (colnum)
View 2 Replies
View Related
Dec 12, 2012
I currently have the following code that works great and deletes the row from my spreadsheet when the word "vacant" appears in column F.
Sub test()
Dim r As Range
With ActiveSheet[code]....
The problem is, "Vacant" also sometimes appears in column G. So, as this code is now it is finding the word "Vacant" in either column F or column G and then deleting the row.
How can I write the code so that it only deletes the row where "Vacant" appears in column F but ignores it when it is in column G?
View 3 Replies
View Related
Nov 15, 2006
Sub Delete()
Dim x As Long
For x = 28 To 8 Step -1
If Range("b" & x) = "" Then _
Range("b" & x).EntireRow.Delete
Next x
End Sub
View 9 Replies
View Related
Dec 24, 2008
I have a large database of customers. I would like to edit this database so that it only lists those customers that have an email address listed in that particular column. I can then save that as a separate list to upload to my marketing campaign.
Is there an easier way than just deleting entire rows at a time? About a 2,000 name list with maybe 30% having email addresses.
View 2 Replies
View Related
May 15, 2009
I'm trying to come up with a macro that can delete an entire column based on formatting of the cells. Basically what I have is a range of cells, and if all cells in a particular column have no fill color, then delete the entire column.
View 9 Replies
View Related
Sep 28, 2013
I plucked a macro off an old post from here (as I always do), but I'm having trouble tweaking it.
VB:
Sub completedtest()
Dim c As Range
Dim rngG As Range
For Each c In Intersect(ActiveSheet.UsedRange, Columns("i"))
[Code] ....
As it currently stands, the macro selects the entire row of rows containing the letter Y in column I. All I need to change is, I want to simply select columns B through I, rather than the entire row.
View 2 Replies
View Related
Jul 18, 2012
Ihave a list of names in column A in certain rows instead of name there is an email address in this case I want to delete the entire row
View 4 Replies
View Related
Feb 24, 2009
hey everyone
i'm using a table like this one :
A B C D
1 Main | value | value | value
2 Extra | value | value | value
3 Main | value | value | value
.
.
n Extra | value | value | value
i want to delete every cell in column "D" only when cell in column "A" contains "Extra" value.
View 6 Replies
View Related
Feb 2, 2014
The below code compares the Data in a Field that must be set and collect the duplicate Values in a second Worksheet.
The thing I want it to copy the rows, when a duplicate is found in Col A. editing the code below:
Original Sheet:
"A" "B" "C" "D"
Teil1A11000
Teil1B21001
[Code]....
View 7 Replies
View Related
Apr 3, 2013
I'm trying to copy entire row from sheet "source" to sheet "output".
Condition: If cell or cells in range (E7: lastcoll, lastrow) value is "A" then copy entire row.
Find the excel template in attachment.
My problem is that my macro is copying particular row, as many times as many "A" finds.
I want to copy entire row just once doesn't matter how many cells with "A" are in particular row.
VB:
'function to find last column a change letter of column to number
Private Function ColLetter(LastCol)
ColLetter = Split(Cells(1, LastCol).Address, "$")(1)
End Function
[Code] .....
copyROW.xlsm
View 3 Replies
View Related
Jul 12, 2007
I tried to writer my code myself but I have a long way to go. Here is what I must do for my case:
I want a macro to find the row that include a specific text (For example "SMSC") in a range (A1:A100) then copy this entire row below the cell which has the value "OTHERS". That is, if there are 10 pieces of "SMSC" so these rows including "SMSC" should be listed below the cell "OTHERS"
View 9 Replies
View Related
Sep 23, 2009
The following sub will look in the file ("FY09 SOF"), in column "A", search for the strings that begin with "2109", "3009", or ends in "-1", and copy the entire row. It will then paste these in the file ("FY09 PR Log Blank").
I also need it to look in column "C" in the ("FY09 SOF") file and pull the entire row if it finds the word "Payroll".
For some reason it will pull everything needed except the "Payroll" rows. What am I doing wrong?
Sub get_data()
Dim wb As Workbook, wbDest As Workbook
Dim ws As Worksheet, wsDest As Worksheet
Dim lngCalc As Long
Dim FoundCells As Range
Dim FoundCell As Range
Set wb = Workbooks("FY09 SOF")
Set wbDest = Workbooks("FY09 PR Log Blank")
Set wsDest = wbDest.Worksheets("Paste all here, then sort")
With Application
.ScreenUpdating = False..........................
View 9 Replies
View Related
Aug 24, 2008
Code that will highlight a entire row when a cell from a column has a value of 0. It will check all the cell from Column C1:C100 and check all the cells that contains a 0 value. Need to highlight the entire row that contains this cell value and fill it in orange color.
View 9 Replies
View Related
May 16, 2009
I have Column A with 380 cells filled in with numbers (A1-A380) i.e. numbers like..456758, 567848 etc etc
Column B has a subset of those numbers, maybe 80-90 or so (B1-B90).
What I want to do is either highlight the numbers in Column A that also appear in Column B in yellow, OR delete them from Column A altogether
View 8 Replies
View Related
May 8, 2014
macro that would keep all lines containing a number greater than zero in column D and delete all of the other lines?
View 9 Replies
View Related
May 2, 2006
I have a workbook where everything is controlled from a single worksheet that I named "Start".
In the "Start" worksheet I have instructions and one command button. When this button is clicked, it will start the VBA code. The code will basically show a couple of userforms where the user will input some information and after the user clicks on a button on the last userform, the code will generate new worksheet and present the results in that new worksheet.
The user could do this as many times as he wants, each time creating a new worksheet or overwriting in an existing worksheet (except the "Start" worksheet). The user is able to delete any worksheets he want.
I need to write a code so that if the user tries to delete the "Start" worksheet, it will not let him/her because then there is no worksheet where I have the button to click to start the VBA code.
I tried protecting the workbook, but then it does not allow to add new worksheets either.
View 9 Replies
View Related
