Delete rows that match criteria


Well-known Member
Jan 30, 2011
Hello all

I am trying to find some VBA code to delete rows that match a criteria but the criteria and column to search can change

I would like a popup box to ask for the criteria, then ask what column to search in
Then delete all the rows that match that criteria
I have tried to put this together but its not correct

Sub RemoveCritera()
Dim c As String
Dim d As String
Dim LastRow As Long
Dim x As Long

Application.ScreenUpdating = False
d = InputBox("Enter column letter", "Remove Duplicates", "A")
c = InputBox("Criteria", "Delete matches", "Criteria")
If c = "" Then Exit Sub
LastRow = Range(d & Rows.Count).End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range(c & "1:" & c & x), Range(c & x).Text) > 0 Then
Range("A" & x).EntireRow.Delete
End If
Next x

Application.ScreenUpdating = True
End Sub

Can someone please help on this

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Could you give me some examples of what your criteria could be?
Upvote 0
Sure Chris

Search column "A"
any cell that has "Saturday" remove the whole row


Search column "C"
any cell that has "Sunday" remove the whole row

So the user would enter the column no in the first popup then enter the criteria in the next popup
To change the search requirement they run the marco again and enter the new details in the popup boxes
Upvote 0
Try this:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> RemoveCritera()<br><br><SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> myCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> myText <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#007F00">'User Inputs</SPAN><br>    myCol = InputBox("Enter column Number", "Remove Duplicates", 1)<br>    myText = <SPAN style="color:#00007F">In</SPAN>putBox("Criteria", "Delete matches", "Criteria")<br><br><SPAN style="color:#007F00">'Handles if no text is entered</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> myText = "" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#007F00">'Filter on MyText</SPAN><br>    <SPAN style="color:#007F00">'Get Data Range</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = ActiveSheet.UsedRange<br>    <br>    <SPAN style="color:#007F00">'Turn on Autofilter</SPAN><br>        rng.AutoFilter<br>        <br>    <SPAN style="color:#007F00">'Filter on text containing myText</SPAN><br>        rng.AutoFilter Field:=myCol, Criteria1:="=*" & myText & "*", Operator:=xlAnd<br>    <br>        <SPAN style="color:#00007F">Set</SPAN> rng = rng.SpecialCells(xlCellTypeVisible)<br>      <br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell In rng.Cells<br>           <SPAN style="color:#00007F">If</SPAN> cell.Row > 1 <SPAN style="color:#00007F">Then</SPAN><br>               cell.Select<br>               Range(Selection, Selection.End(xlDown)).Select<br>               Selection.EntireRow.Delete<br>               <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>           <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> cell<br>    <br>    <SPAN style="color:#007F00">'Unfilter Worksheet</SPAN><br>        ActiveSheet.AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br><br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back