Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows which match filter
I want to delete all the rows which match a selected filter. Currently I
have: Selection.AutoFilter Field:=6, Criteria1:="0" Rows("2:1517").Delete Shift:=xlUp It works great as long as I don't have more than 1517 rows. How do I change it so I specify all data rows in the worksheet (same as an end,down would do)? -- Thanks, Fred Please reply to newsgroup, not e-mail |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows which match filter
Selection.AutoFilter Field:=6, Criteria1:="0"
set rng = Activesheet.Autofilter.Range set rng = rng.offset(1,0).Resize(rng.rows.count-1) rng.Delete Shift:=xlup we need to shift down one row so we don't delete the header row. -- Regards, Tom Ogilvy Fred Smith wrote in message ... I want to delete all the rows which match a selected filter. Currently I have: Selection.AutoFilter Field:=6, Criteria1:="0" Rows("2:1517").Delete Shift:=xlUp It works great as long as I don't have more than 1517 rows. How do I change it so I specify all data rows in the worksheet (same as an end,down would do)? -- Thanks, Fred Please reply to newsgroup, not e-mail |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows which match filter
Fred Smith,
Try, Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row) _ ..SpecialCells(xlVisible).Delete Shift:=xlUp HTH Cecil "Fred Smith" wrote in message ... I want to delete all the rows which match a selected filter. Currently I have: Selection.AutoFilter Field:=6, Criteria1:="0" Rows("2:1517").Delete Shift:=xlUp It works great as long as I don't have more than 1517 rows. How do I change it so I specify all data rows in the worksheet (same as an end,down would do)? -- Thanks, Fred Please reply to newsgroup, not e-mail |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
Data filter and deleting rows in visual basic | Excel Discussion (Misc queries) | |||
Filter PivotTable dropdown items to match report filter | Excel Discussion (Misc queries) | |||
Deleting rows in filter mode - Excel 2007 | New Users to Excel |