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

 

[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.)

 

[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.