Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to delete rows for only filtered results
The extract below is unable to run when the criteria for the
Autofilter results in the number of rows being in excess of around 15800. Selection.AutoFilter Field:=6, Criteria1:="=" Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete Even when attempted manually, the "delete rows" function will not work on a filter and deletes ALL data (both hidden and otherwise). Hence my subject, the macro won't delete only rows for filtered results where the filter produces too many rows. I hope I've made myself understood. Is there a workaround? Cheers |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to delete rows for only filtered results
i've come across this issue before - there the list seems to large for the
auto-filter to work as expected. you could write some code to delete hidden rows.. if your table is range named (UNTESTED) dim cell as range for each cell in range("tablename").columns(1).cells if rows(cell.row).Visible=False then rows(rw).Delete next "Richard R" wrote in message ... The extract below is unable to run when the criteria for the Autofilter results in the number of rows being in excess of around 15800. Selection.AutoFilter Field:=6, Criteria1:="=" Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete Even when attempted manually, the "delete rows" function will not work on a filter and deletes ALL data (both hidden and otherwise). Hence my subject, the macro won't delete only rows for filtered results where the filter produces too many rows. I hope I've made myself understood. Is there a workaround? Cheers |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to delete rows for only filtered results
Try recording a macro when you select the visible data cells and hit
F5|Special|visible cells only. Then continue recording when you delete those rows. '...your code to filter the data with ActiveSheet With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then 'only headers visible, do nothing Else 'resize to avoid the header 'and come down one row .Resize(.Rows.Count - 1, 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) _ .entirerow.delete End If End With End With Richard R wrote: The extract below is unable to run when the criteria for the Autofilter results in the number of rows being in excess of around 15800. Selection.AutoFilter Field:=6, Criteria1:="=" Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete Even when attempted manually, the "delete rows" function will not work on a filter and deletes ALL data (both hidden and otherwise). Hence my subject, the macro won't delete only rows for filtered results where the filter produces too many rows. I hope I've made myself understood. Is there a workaround? Cheers -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to delete rows for only filtered results
hi all, Dave's suggestion is good in terms of preventing header deletion but is still fallible in terms of a "by design" limitation of the specialcells property (see 'here' (http://support.microsoft.com/kb/832293/en-us)). Here's a link suggesting various workarounds (which you may need help implementing...): 'Bulk Row Deletion - Fastest method in Excel Programming' (http://tinyurl.com/nkm5fl) hth Rob -- broro183 Rob Brockett. Always learning & the best way to learn is to experience... ------------------------------------------------------------------------ broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=107832 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to delete rows for only filtered results
I couldn't get to the second site you posted.
But I've found that a quick way to delete those rows is to add an extra column that mimics the rules of the filter -- returning true or false. Then sort by this column and delete the contiguous range (either true or false). If I need to resort when I'm done, I make sure that there is a nice key or add a second column that provides the nice sort order. (and then remove those columns when done.) broro183 wrote: hi all, Dave's suggestion is good in terms of preventing header deletion but is still fallible in terms of a "by design" limitation of the specialcells property (see 'here' (http://support.microsoft.com/kb/832293/en-us)). Here's a link suggesting various workarounds (which you may need help implementing...): 'Bulk Row Deletion - Fastest method in Excel Programming' (http://tinyurl.com/nkm5fl) hth Rob -- broro183 Rob Brockett. Always learning & the best way to learn is to experience... ------------------------------------------------------------------------ broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=107832 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to delete rows for only filtered results
hi Dave, Nice work around :) I think the second link provides an interesting read so here are some alternative links to the same thread: Shortened version of previous link: 'Bulk Row Deletion - Fastest method in Excel Programming' (http://tinyurl.com/lsmyhh) Same thread on a different server: 'Excel Bulk Row Deletion - Fastest method' (http://www.databaseforum.info/16/8/6...0964c7cb0.html) Link to one post in the thread: 'microsoft.public.excel.programming: Bulk Row Deletion - Fastest method' (http://tinyurl.com/kq9uba) or straight from my search - any of the links shown by: 'specialcells excel limit\"bulk row deletion\" \"fastest method\" - Google Search' (http://tinyurl.com/lljkg9) hopefully one of these links works :) Rob -- broro183 Rob Brockett. Always learning & the best way to learn is to experience... ------------------------------------------------------------------------ broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=107832 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to delete rows for only filtered results
That first suggestion by quartz suffers the same problem if there are too many
areas. The suggestion by Stephen Bullen is about the same as I suggested. broro183 wrote: hi Dave, Nice work around :) I think the second link provides an interesting read so here are some alternative links to the same thread: Shortened version of previous link: 'Bulk Row Deletion - Fastest method in Excel Programming' (http://tinyurl.com/lsmyhh) Same thread on a different server: 'Excel Bulk Row Deletion - Fastest method' (http://www.databaseforum.info/16/8/6...0964c7cb0.html) Link to one post in the thread: 'microsoft.public.excel.programming: Bulk Row Deletion - Fastest method' (http://tinyurl.com/kq9uba) or straight from my search - any of the links shown by: 'specialcells excel limit\"bulk row deletion\" \"fastest method\" - Google Search' (http://tinyurl.com/lljkg9) hopefully one of these links works :) Rob -- broro183 Rob Brockett. Always learning & the best way to learn is to experience... ------------------------------------------------------------------------ broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=107832 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COPY PASTE with FILTER copies anti-filtered results as hidden rows | Excel Programming | |||
Delete all filtered rows from a macro? | Excel Programming | |||
cannot delete filtered rows in excel 2007 | Excel Discussion (Misc queries) | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
Excel - How do I delete filtered out rows? | Excel Discussion (Misc queries) |