How to delete rows containing certain keywords in cells
Requirement:
The user is working on a macro to filter rows based on certain keywords in cells and then delete selected rows (which contain those words) and keep remaining rows + header row.
The user's objective is to:
- filter rows those contain these keywords...."Diesel", "Lub", "Key" for entire range of rows in a column
- Delete filtered rows containing above words and keep remaining rows + header row
Key words for filtering will remain same even if no of rows increase and change in other text.
Range will vary, can go up to 70,000 rows
The user has tried on below but it does not work when no of rows are more. Sample file attached.
Code:
Sub VKMacro1()
Range("D1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
("D1:D373"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$1:$H$373").AutoFilter Field:=4, Criteria1:= _
"=DOOR KEY", Operator:=xlOr, Criteria2:="=MAIN KEY"
Range("D29").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
ActiveSheet.Range("$A$1:$H$328").AutoFilter Field:=4
ActiveSheet.Range("$A$1:$H$328").AutoFilter Field:=4, Criteria1:="=ENG LUB" _
, Operator:=xlOr, Criteria2:="=TM LUB"
Range("D29").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
ActiveSheet.Range("$A$1:$H$304").AutoFilter Field:=4
ActiveSheet.Range("$A$1:$H$304").AutoFilter Field:=4, Criteria1:= _
"=HIGH SPEED DIESEL", Operator:=xlOr, Criteria2:="=NORMAL DIESEL"
Range("D125").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
ActiveSheet.Range("$A$1:$H$280").AutoFilter Field:=4
End Sub
Solution:
Click here for solution provided by Carim:
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to delete rows with no value |
| How to loop each row if there is data after green colour cell then delete |
| How to use VBA to delete rows based on multiple conditions |
| How to use Excel VBA to delete rows in a column based on a range of cells in another column |
| How to count and delete duplicate entries over multiple columns |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
