Try this macro.

Option Compare Text
Sub Delete_By_Criteria()
Dim i As Integer
Dim iLastRow As Integer

Set wks = ActiveSheet
Application.ScreenUpdating = False
ActiveSheet.Range("B:B").Select

whatwant = InputBox("Choose Criteria" & Chr(13) _
& "Wildcards such as *co* can be used")

iLastRow = wks.Cells(Rows.Count, 2).End(xlUp).Row
For i = iLastRow To 1 Step -1
If Not wks.Cells(i, 2).Value Like whatwant Then
wks.Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True
Exit Sub

End Sub


Gord

On Thu, 26 Jul 2007 18:46:00 -0700, simplymidori
wrote:

Thanks Gord - I was actually specifically looking for a popup to do this
function. I have a sheet that I need to create new tabs from each new
change in column B. It seems like the filter process takes too much time for
me. I would rather type what I want to keep and delete the rest.



"Gord Dibben" wrote:

DataFilterAutofilterCustom on column B

"does not equal" or "does not contain" color

With those "color" rows filtered out, select the visible remaining and
EditDelete Row

If you want a macro, record whilst doing the above.


Gord Dibben MS Excel MVP

On Thu, 26 Jul 2007 18:12:02 -0700, simplymidori
wrote:

Is there a macro I can use to remove data across the entire row depending
what has been typed in a popup msg box to ask "What would you like to keep?".
Have it
look at a column with such fields to filter.

Here I would want to only keep "color" and the remainder rows get deleted.

A B
black color
red color
blue color
yellow color
bat object
coat object
box object

Thanks in advance,

SimplyM