VBA code for if no visible cells after filter goto next stage

  • Thread starter Thread starter thomsonpa
  • Start date Start date
T

thomsonpa

I an running filters in VBA then copying the results to another worksheet on
the next available line. I have a challenge, that when there is nothing to
copy (i.e. the filter results are blank), I cannot get the VBA to go to the
next stage.

I have tried

Selection.AutoFilter Field:=19, Criteria1:="<>"

Sheets("IMPORT").Activate
Sheets("IMPORT").Range("B2:B120").Select

If Selection.SpecialCells(xlCellTypeVisible) Then

'do something

else

'do something else

end if

But this results in an error.

Any help please.
 
Hi,

If you try and select visible cells in a filtered range and there are none
an error is generated and you can use this

With Sheets("IMPORT")
Selection.AutoFilter Field:=19, Criteria1:="<>"
On Error GoTo enditall
Set rng = .AutoFilter.Range.Offset(1,
0).Resize(.AutoFilter.Range.Rows.Count - 1, 1) _
..SpecialCells(xlCellTypeVisible)
End With
'rng exists
'Do things
enditall:
'no visible cells
'do something else
 
Looks like this will work, thank you very much. The enditall was what I was
looking for, I will use this again in future.
I don't know what I would do if it wasn't for this discussion group. It
helps me with so much.

Thank you, and keep up the good work.
 
Back
Top