Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if it does not contain
Tom, thank you very much
Greg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |