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

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

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

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

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

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

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