Unable to delete rows for only filtered results

[Richard R]

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

 

[Patrick Molloy]

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

 

[Dave Peterson]

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

 

[broro183]

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

 

[Dave Peterson]

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]

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/62e2ae60964c7cb0.html)

Link to one post in the thread: 'microsoft.public.excel.programming:
Re: 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

 

[Dave Peterson]

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.