when removing filter all data disappears

**rinser** · 12-07-2009, 11:48 AM

Hi All,

I have a filter in my code that is supposed to filter data by a certain criteria, delete the filtered results and afterwards eliminate filter so that remaining rows would be seen by the next module.
The thing is it does filter correctly, but then if there is no data to delete (no data meets filter criteria), after removing the filter there is actually no data at all - it deletes the rows hidden by filter, it seems. If there is data that meets criteria and should be deleted then it deletes it and after removing the filter the data is ok. The problem arises when there is nothing to delete, it just deletes the hidden (by filter) rows.
Here is the code (the filter part is in bold):

Please Login or Register  to view this content.

This is a sample of the data:
Sample sorting.xls

Please advise me as to what is wrong with this code or how can it be improved. Any ideas appreciated.

**StephenR** · 12-07-2009, 12:28 PM

See if this works.

Please Login or Register  to view this content.

**rinser** · 12-07-2009, 01:51 PM

Hi Stephen,

Thank you for fast response. I have tried the code and it seems to be doing the job. I will test it more tomorrow and get back.

Cheers.

**rinser** · 12-11-2009, 05:13 PM

Hi Stephen,

I made some testing and it seems to work. Sometimes (when there are only a few rows) I got an error, after removing On Error GoTo 0 it seems to work fine. Is it safe to remove the On Error GoTo 0?

Cheers.

**rinser** · 12-17-2009, 06:42 AM

Stephen,

Your solution works so I am closing the thread.

Thanks for help!

**cartedor** · 01-15-2015, 06:09 PM

I have similar issue
whereby I am trying to delete filtered rows

if the filters leave rows of data, it deletes them without any problem
but the issue occurs when there was no results being returned from the filters, so effectively, just the heading row is visible

and my vba code throws up an error and highlight my code in yellow.

With .AutoFilter.Range
On Error Resume Next
Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rData Is Nothing Then
rData.Delete shift:=xlUp
End If
End With
.AutoFilterMode = False

so is this part of the code gets around the error?