CODEWITHSUNDEEP
excelvba
Pherdindy
Pherdindy

Reputation: 1178

How to let my VBA code skip the Application.Intersect function when there are no cells for the filter criteria?

How do I let my my code skip the Application.Intersect function when there are no data points for the filter criteria? The error occurs at line 3 and 4 stating: Error

I tried the (on line 3): If IsError(Intersect(filterRange, filterRange.Offset(1, 0)).SpecialCells(xlCellTypeVisible)) = False Then

But it does not work. I also tried: If Not IsError(Intersect(filterRange, filterRange.Offset(1, 0)).SpecialCells(xlCellTypeVisible)) Then

Set filterRange = Worksheets("JO_Pickups (Test)").Range("$A$1:$E$" & Total_rows_JOPickTest) 'filter for less computations
filterRange.AutoFilter Field:=2, Criteria1:=JO_id(i)
If IsError(Intersect(filterRange, filterRange.Offset(1, 0)).SpecialCells(xlCellTypeVisible)) = False Then
    Set filteredRange = Intersect(filterRange, filterRange.Offset(1, 0)).SpecialCells(xlCellTypeVisible) 'remove headers
    If Not filteredRange Is Nothing Then
        For Each Area In filteredRange.Areas
            Worksheets("JO_Pickups (Test)").Range(Area.Address).Copy _
            Destination:=Worksheets("Help Worksheet").Cells(2, 1)
        Next Area
    End If
End If

Upvotes: 0

Views: 191

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

You can't trap a runtime error using IsError(), so you need something like:

Dim visRng As Range

On Error Resume Next
Set visRng = filterRange.Offset(1, 0)).SpecialCells(xlCellTypeVisible)
On Error Goto 0

If Not visRng is Nothing Then
    'do stuff...
End If

Upvotes: 1

Related Questions