Deleting Data based on Criteria and Wild Cards

[Karen Schomer]

How can I delete rows of data using criteria/wildcards?

Example: Data is:
Coke Cola / Alabama
Coke Cola - New Jersey
7-Up / California
Coke Cola (Washington)
7-Up - New York
Root Beer / Florida

Result: Delete all the Coke Cola*

 

[Tazguy37]

Welcome to the Board!

You could sort by that column and delete the records you want, or use Data, Autofilter to show only the relevant rows, then delete them. Hope that helps!

 

[HalfAce]

Hi Karen, welcome to the board.

If the values you've shown in your example are all in one column then the autofilter won't do you much good. (Will be great if they're not in one column...) but if they are, then maybe something along these lines is in order. (Assumes they are in column A...)

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DeleteCokeColaRows()
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
x = Cells(65536, 1).End(xlUp).Row
<SPAN style="color:#00007F">For</SPAN> i = x <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1
    <SPAN style="color:#00007F">If</SPAN> InStr(Cells(i, 1), "Coke Cola") <SPAN style="color:#00007F">Then</SPAN> Cells(i, 1).Rows.EntireRow.Delete
<SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope it helps,
Dan