Delete row if it does not contain
 

I have a table with many rows. I want to delete all rows that do not
contain "*computers and phones*" in column (6). Rows 1 and 2 are header
rows. How do I proceed? TIA

Greg

Delete row if it does not contain
 

Dim r as Range
Dim lastRow as Long
Dim i as Long
set r = Activesheet.UsedRange
lastrow = r.rows(r.rows.count).Row
for i = lastrow to 3 Step -1
if Instr(1,cells(i,6),"computers and phones", _
vbTextCompare) = 0 then
rows(i).Delete
end if
Next

--
Regards,
Tom Ogilvy

"GregR" wrote in message
oups.com...
I have a table with many rows. I want to delete all rows that do not
contain "*computers and phones*" in column (6). Rows 1 and 2 are header
rows. How do I proceed? TIA

Greg

Delete row if it does not contain
 

Just an additional suggestion for Greg,

you might want to include code to have the macro copy the current sheet
to a new sheet and make the changes to the new sheet. Some code fragments in
http://www.mvps.org/dmcritchie/excel/sheets.htm

Often you can use a filter instead of making additional copies or variations.
http://www.mvps.org/dmcritchie/excel/excel.htm

Delete row if it does not contain
 

Tom, thank you very much

Greg

Delete row if it does not contain
 

"GregR" wrote in news:1129824703.024847.179940
@g47g2000cwa.googlegroups.com:

I have a table with many rows. I want to delete all rows that do not
contain "*computers and phones*" in column (6). Rows 1 and 2 are header
rows. How do I proceed? TIA

Greg




Greg,

I would do this manually by:

Turning on Autofilter (Data - Filter - AutoFilter)
Apply a custom filter on column 6 (does not contain)
Select the visible cells in column 6 that I want to remove
Edit - Delete Row


Here's a procedure that does the same thing:

Sub DeleteRows()
Dim rng As Range
Set rng = Sheet1.Range("F2:F6")

Sheet1.AutoFilterMode = False
rng.AutoFilter Field:=1, Criteria1:="<*computers and phones*"

Range(rng.Offset(1, 0), rng.Offset(rng.Rows.Count, 0)) _
.SpecialCells(xlCellTypeVisible).EntireRow.Delete

Sheet1.AutoFilterMode = False

Set rng = Nothing
End Sub


Note that I started in the second row so that the headings are not lost.
Don't know if this is faster than Tom's loop.


HTH,
RP