Delete rows that match criteria

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,220
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

Change

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

Threads
1,196,487
Messages
6,015,485
Members
441,898
Latest member
kofafa

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top