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

"thomsonpa" wrote:

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.

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.

"Mike H" wrote:

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

"thomsonpa" wrote:

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.