Macro - How To Delete Rows With Specific Criteria
May 21, 2012
I was searching for a macro to delete rows that obey a specific criteria. I downloaded [URL]..... and copied the Loop Example to my WorkBook.
When using Mr. de Bruin example, it worked perfectly, it did really delete the rows that obeyed my ">2" criteria. Although, when using on my workbook, the macro deleted the values, not the entire rows. Here's what's happening:
I would like to delete rows, not only values.
Code:
'The code below will delete every row in the usedrange with "ron" in the A column.
'If .Value = "ron" Then .EntireRow.Delete
'
'I use the A column in my example, change the A to your column in this code line.
'With .Cells(Lrow, "A")
'
'Change ron and the A to your search word and column before you try the macro.
'See the examples on the worksheet if you want to do something different.
Sub Loop_Example()
Dim Firstrow As Long
[Code]....
View 2 Replies
ADVERTISEMENT
Nov 29, 2011
I have a sheet of data. The data starts in row 4 and can be variable in length. I want to delete rows that do not meet a specific criteria, for example a location, Leeds, which is in column "i" of the table. I have written the below but it seems to delete data from row 1 rather than row 4 and repeat down each row
Code below
Sub deleterows()
Dim i As Integer
Dim intCounter As Integer
Dim strLocation() As String
p = Range(Cells(4, 6), Cells(4, 6).End(xlDown)).count
[Code] ..........
View 3 Replies
View Related
May 7, 2012
Each week I run a report that produces over 2,000 rows. I have to manually go through and delete things, by the time I am done, there may only be 320 rows left. I would apply a recorded macro, but the report is not consistent each week. There may be more or fewer rows depending on the week.
I would like to be able to run a macro that looks in column B for a specific value and if it contains that specific value, I would like the cells to the right of it to have their contents cleared.
Next, in column C, I have...
Employee2's Name
Employee2's Number
SPACE
[Code]....
I would like to be able to delete the rows, starting from Employee 2 to just before the second SUB TOTAL.
So, all I would have left in column C is
SUB TOTAL
SPACE
View 9 Replies
View Related
Jan 27, 2009
I need to build a macro which will look for a specific word say :"ABC" in a particular column say "B" and delete 5 rows including the row containing the word "ABC"
View 6 Replies
View Related
Jul 12, 2014
I currently had to start from scratch as my formula kept raising the debug screen. I know the answer is simple but I cant figure out what it is. I have column A, rows 9 through 1000 with data. I want to delete the entire row of any "x" value present in column A rows 9 through 1000. I am new to VBA. So simple is good.
View 2 Replies
View Related
Aug 8, 2007
I have macro code to hide rows based on a certain critera but I don't know how to adapt the code to delete the rows. Basically it checks a column and for every row
View 9 Replies
View Related
Aug 26, 2008
I am not the best at this, but we recorded a macro and we want to delete whole rows after we 'control' find something from a specific column. WHen we recorded our macro, it finds the first instance and we hightlight the whole row and 'control' 'shift' 'end' and delete all of the rows. We did this because we sorted and made sure the information we wanted to delete was at the bottom of the worksheet. we realized that each day the spreadsheet we pull from an ip address gets bigger and the row that we started from to delete starts on a different row each day. How do we get all the information to delete everyday, even when the row that Owned starts on changes? ...
View 9 Replies
View Related
Feb 23, 2011
how they can be used to delete rows that meet multiple criteria, however all the examples I have found seem to be rather complex and personal to the individuals as opposed to being rather generic. Many people, including myself would find it useful to have a simplified generic answer that can be applied to all kinds of criteria. Such as if the data in column B = x and the data in column C = y delete the row. Here is my example.
SCENARIO EXAMPLE:
In column A - Date e.g. 05/02/2011, 06/02/2011
In column B - Day
In column C - Time (in format 09:06:21)
In column D – Service numbers dialled (70, 110, 224) two to three digits long
MACRO EXAMPLE:
Delete the row if the criteria in:
Column D = 10, 40, 192 or 244
And
Column C is between 09:00:00 and 16:00:00
macro code or at least a link to a thread that they think gives a scenario as simple as this.
View 5 Replies
View Related
Jul 10, 2014
A macro is required to identify rows within a selection e.g. entire column A, that share the same value, then delete appropriate rows depending on the values in another column. The attached example details the requirements.
Extract Rec1.xlsx
View 6 Replies
View Related
Apr 9, 2009
I need a macro to delete old data from a large selection of data, in order to keep the size down.
What I want this macro to do is
Check all rows from 5 downwards.
If A5 (date) is less than cell $B$1 AND B5 is not equal to C5 then delete the whole row.
Continue until reaching the bottom.
View 9 Replies
View Related
Feb 28, 2014
I am trying to adapt a macro which deletes all rows in Worksheet 1 where the date in column K differs from a date in Worksheet 2. The date in Worksheet 2 will change each day.
I think the macro below could be used, but don't understand how to replace the "Value = ":70:" with a reference to the master date in Worksheet 2.
Sub test() Dim lr As Long, i As Long lr = Range("A1").End(xlDown).Row
For i = lr To 1 Step -1 If Cells(lr, 1).Value = ":70:" Then
Cells(lr, 1).EntireRow.Delete End If lr = lr - 1 Next i End Sub
View 2 Replies
View Related
Dec 10, 2007
Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):
Find_Range("A", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete
although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:
Range("1:65536").Select
For Each cl In Range("J:J")
If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then
Rows(cl.Row).Delete
End If
Next
but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...
View 5 Replies
View Related
Aug 28, 2007
I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:
1- all rows with the value "TOM" in column C will have to be deleted.
2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).
View 2 Replies
View Related
Jul 27, 2012
I created the following macro to remove all rows that contain the value 'Shutdown" in column F
Code:
Sub DeleteUniqueValues()
Dim LR As Long, i As Long
LR = Range("F" & Rows.Count).End(xlUp).Row
For i = LR To 3 Step -1
With Range("F" & i)
If WorksheetFunction.CountIf(Columns("F"), .Value) = Shutdown Then .EntireRow.Delete
End With
Next i
End Sub
The macro runs without error, but when I checked the spreadsheet, rows matching this criteria were not deleted.
View 4 Replies
View Related
Sep 13, 2007
is there a code to chose certain rows using there numbers (Row number 3 to row number 9) to be deleted? yes, there is a code to delete the selected rows, but what i am after is a code to chose rows by thier numbers like delete from row3 to row9?
View 7 Replies
View Related
Jul 29, 2012
I'm looking for a code where all those rows which contain some identifier be deleted.
Eg, If any row contains identifiers like "----" , "PROG:" , etc shall be deleted.
NOTE: The identifiers are found on the A Column of any row.
All rows which do not contain the above identifiers shall remain untouched.
View 9 Replies
View Related
May 7, 2013
I need a macro in excel which work like that "To delete all rows without specific text". and following macro for this;
What I need in this is that i have more than one values (highlighted with red in formula) so how i will do in this?
Sub DeleteRows()
Dim lRow As Long
Dim lLastRow As Long
'Change "A" to suit
lLastRow = Cells(Rows.Count, "A").End(xlUp).Row
[Code] .....
View 3 Replies
View Related
Jun 2, 2008
I have 2 spreadsheets of delivery information. Sheet 1 contains data of all stores in the UK with addresses. Sheet 2 containts contacts of all stores that promotional matrial needs to be sent to.
The only common cell in a column of 4 digit store numbers.
I need to delete all rows from sheet 1 that do not contain a value in the store number column that is displayed on sheet 2.
View 11 Replies
View Related
Nov 1, 2012
Code in VBA which I can auto delete many specific rows.
example: in column B got the word "Tester, Dummy, Display and Mini.."
I would like a code which automatically delete the rows which contain the text as above.
View 6 Replies
View Related
Feb 27, 2013
If I have a column of data called "ColStream" and want to delete all rows that don't contain the string "Production", whats the best way to achieve it?
I have tried a for each loop, with INSTR but as this moves down a cell from the cell whose row has just been deleted, skipping the "NEXT" row in the range, it doesn't give the correct results.
Incidentally, the end game is to copy all the remaining cells to the cell immediately below a cell called "StreamsDatabaseStart" in a worksheet called "ProdStreams" should anyone out there be feeling particularly charitable!
View 4 Replies
View Related
Feb 23, 2004
I need a formula that finds a value or range of values in a list and if the value meets the criteria automaticaly delete that row. For example I want to search B3:B7 for a 0 value if I find a cell in that range with a 0 value i want the formula to delete the entire row.
View 4 Replies
View Related
Jan 31, 2008
A macro that will delete a tab or tabs in a file if and only if rows 11, 13, 23 & 25 are completely blank within that tab?
So basically ALL rows would need to be blank, if there is any data within any of those rows, then tab should NOT be deleted.
View 9 Replies
View Related
Nov 10, 2006
how to create one macro: I have a spreadsheet with 8 columns with various text headings. Each column has different data (some show dates, some show only figures, some show text). Specifically, column E (entitled "Sub Item Reference") has various text entries (e.g. "Newsletter" or "Booklet"). I am hoping to find a simple macro that will search column E and delete any rows where that column contains the text "Cover" or "Label". I'm sure it's the simplest thing going but I can't seem to work it out.
View 7 Replies
View Related
Jun 27, 2008
I have a code that goes through and deletes everything that doesn't contain a certain word, but I have several words ( Names actually) that I need to keep in my sheet, but all others can be deleted, along with their rows...the names are on in Column B...there are about 11 names currently and probably be adding a few more next month...This is a macro that is set for work to go through like I said a lot of Rows and delete all them but those that have the names of the workers I need to keep in the sheet...the names are scattered throughout, which is the reason for a need of a macro to do it for me cause it takes me forever to manually do it each day...The code for just one person I can't get to do multiple names so this is what I came up with based off what I know plus reading hundreds of pages on the net regarding similar style needs, but none exactly the same....
This is the Code that I'm trying to use but unfortunately I get Type Mismtch Error 13 ( I think it's error 13)...what am I doing wrong? (I only am working with one sheet that contains all the data...
Sub SortOutMyTeam()
Dim wst As Worksheet, wstNew As Worksheet
Dim rData As Range, r As Range, rTotal As Range
Dim avNames() As Variant
avNames = Array("Name1", "Name2", "Name3", "Name4", "Name5")
Set wst = ActiveSheet
Set rData = Range("B1:BL" & Cells(Rows.Count, 1).End(xlUp).Row)
Application. ScreenUpdating = False
wst.AutoFilterMode = False
With rData
For i = 0 To UBound(avCrit) Step 2
. AutoFilter field:=3, Criteria1:=avNames(i), Operator:=xlOr, Criteria2:=avNames(i + 1)
On Error Resume Next
Set r = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells
View 5 Replies
View Related
Mar 5, 2008
This sheet has A:K columns and 1:3212 rows. There are 'page headers' that are in the text file that I want to delete (the text file was exported from an AS400 program). The first row that starts the page header has SA341 in column 1. Each page header has 5 rows. I used this code from one of the other threads on deleting rows, but I obviously do not understand the code as it deleted all rows that contained SA341.
Sub DeleteRows()
Column_To_Check = 1
Start_Row = 1
End_Row = ActiveSheet. Cells(Rows.Count, Column_To_Check).End(xlUp).Row
MsgBox End_Row
Search_String = "SA341"
For Row_Counter = End_Row To Start_Row Step -1
If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value < SA341 > Search_String Then
ActiveSheet.Rows(Row_Counter).Delete
End If
Next Row_Counter
End Sub
View 9 Replies
View Related
Jan 5, 2010
I have a large report that I pull each month listing which products we sold last month. There are certain products I don't want included in the report. Until I can have the report re-written, I'm looking for a macro or a function that will find all the rows that contain these product numbers in column M and delete the entire row. The product numbers I need to delete are always the same, but some months one or more of them might not be in the report if they weren't sold.
In Summary:
Find in column M the product numbers:
c1000
316140a
316140
316295a
316295
316311a
316311
316451a
316451
316450a
316450
316452a
316452
Delete those rows containing the product numbers above
View 6 Replies
View Related
Aug 6, 2014
I'm trying to write a macro to update a report including deleting all rows that do not contain a specific value of TP in column K. Below is one of the examples.
[Code] .....
View 3 Replies
View Related
Feb 28, 2014
create a VBA to delete rows if the cell doesn't have "Other".
The data is in Column C which consist of the below data so I like VBA to delete the rows when the cell doesn't have "Other"..
-Primary
-Business
-Home
-(blank)
-Other
View 5 Replies
View Related
Apr 13, 2009
I need a code that will go thru column C and delete the whole row if cell value is 78315, Also it needs to shift the the rows up so there is no empty rows wheneverything is done done. I used some codes but they take forever to go thru whole sheet.
View 4 Replies
View Related
Mar 8, 2012
Is there a VBA code I can use that will delete all rows that contain "ABC" or "YXZ" or "HHH" in column A ?
View 2 Replies
View Related