Note: Code belong to this webpage https://macexcel.com/examples/othertopics/deleterows/ ****************************** Use AutoFilter to delete rows ****************************** Tip: with AutoFilter you can also use wildcards. "*ron" End with ron "ron*" Start with ron "*ron*" ron is a part of the string You can also use the wildcard ? for a single character. Note: in the examples I use the range .Range("A1:A" & .Rows.Count) Remember that A1 of this range is your header cell. ***One criteria*** The example below filter A1:A? on the ActiveSheet for the DeleteValue and delete the rows. Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range Dim calcmode As Long With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Fill in the value that you want to delete 'Tip: use DeleteValue = "<>ron" to delete rows without ron DeleteValue = "ron" 'Sheet with the data, you can also use Sheets("MySheet") With ActiveSheet 'Firstly, remove the AutoFilter .AutoFilterMode = False 'Apply the filter .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=DeleteValue With .AutoFilter.Range On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End With 'Remove the AutoFilter .AutoFilterMode = False End With With Application .ScreenUpdating = True .Calculation = calcmode End With End Sub ***Two criteria*** The example below filter A1:A? on the ActiveSheet for the DeleteValue1 and 2 and delete the rows. Sub Delete_with_Autofilter_Two_Criteria() Dim DeleteValue1 As String Dim DeleteValue2 As String Dim rng As Range Dim calcmode As Long With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Fill in the two values that you want to delete DeleteValue1 = "ron" DeleteValue2 = "jelle" 'Sheet with the data, you can also use Sheets("MySheet") With ActiveSheet 'Firstly, remove the AutoFilter .AutoFilterMode = False 'Apply the filter .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, _ Criteria1:=DeleteValue1, Operator:=xlOr, Criteria2:=DeleteValue2 With .AutoFilter.Range On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End With 'Remove the AutoFilter .AutoFilterMode = False End With With Application .ScreenUpdating = True .Calculation = calcmode End With End Sub Example to filter between two dates, you not use the DeleteValue1 and 2 string then. This will delete all records in 2006. (Note: I use xlAnd here). You can use this also if you want to filter numeric values. .AutoFilter Field:=1, Criteria1:=">=" & DateSerial(2006, 1, 1), _ Operator:=xlAnd, Criteria2:="<" & DateSerial(2007, 1, 1) ' yyyy-mm-dd format ***More then two Criteria*** For more then two Criteria you can loop through the words in the array. Note: This is also working if you use one word in the array. Sub Delete_with_Autofilter_Array() Dim rng As Range Dim calcmode As Long Dim myArr As Variant Dim I As Long With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Fill in the values that you want to delete myArr = Array("ron", "Dave", "Jelle") For I = LBound(myArr) To UBound(myArr) 'Sheet with the data, you can also use Sheets("MySheet") With ActiveSheet 'Firstly, remove the AutoFilter .AutoFilterMode = False 'Apply the filter .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I) Set rng = Nothing With .AutoFilter.Range On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End With 'Remove the AutoFilter .AutoFilterMode = False End With Next I With Application .ScreenUpdating = True .Calculation = calcmode End With End Sub **Criteria range on a different sheet*** Example with the criteria range on a different sheet The example below filter A1:A? In a sheet named “data”. And use as criteria all the cells in column A of a sheet named “Criteria”. Note: You can use also wildcards like *food* or *store if you want. Sub Delete_with_Autofilter_More_Criteria() Dim rng As Range Dim cell As Range Dim CriteriaRng As Range Dim calcmode As Long With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With Sheets("Criteria") Set CriteriaRng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp)) End With 'Loop through the cells in the Criteria range For Each cell In CriteriaRng With Sheets("data") 'Firstly, remove the AutoFilter .AutoFilterMode = False 'Apply the filter .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=cell.Value With .AutoFilter.Range Set rng = Nothing On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End With 'Remove the AutoFilter .AutoFilterMode = False End With Next cell With Application .ScreenUpdating = True .Calculation = calcmode End With End Sub ************************ Use Find to delete rows ************************ Change this three lines in the macro before you test it. 'We use the ActiveSheet but you can also use Sheets("MySheet") Set sh = ActiveSheet 'We look in column A in this example Set myRng = sh.Range("A:A") 'Add more search strings if you need myStrings = Array("Ron", "Dave", "Tom") Sub Find_Example() Dim calcmode As Long Dim ViewMode As Long Dim myStrings As Variant Dim FoundCell As Range Dim I As Long Dim myRng As Range Dim sh As Worksheet With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can also use Sheets("MySheet") Set sh = ActiveSheet 'We search in column A in this example Set myRng = sh.Range("A:A") 'Add more search strings if you need myStrings = Array("Ron", "Dave", "Tom") With sh 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'We will search the values in MyRng in this example With myRng For I = LBound(myStrings) To UBound(myStrings) Do Set FoundCell = myRng.Find(What:=myStrings(I), _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) 'Use xlPart If you want to search in a part of the FoundCell 'If you use LookIn:=xlValues it will also delete rows with a 'formula that evaluates to "Ron" If FoundCell Is Nothing Then Exit Do Else FoundCell.EntireRow.Delete End If Loop Next I End With End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = calcmode End With End Sub ******************************* Use Specialcells to delete rows ******************************* You can find information on this page. http://rondebruin.nl/win/s4/win003.htm