CODEWITHSUNDEEP
excelvba
user3255246
user3255246

Reputation: 45

SpecialCells(xlCellTypeVisible) - Error when Autofilter returns zero rows

Below is a portion of my code. My code will throw an error when the Autofilter for the criteria returns Zero rows. I tried the row.count but does not help. Is there a way to workaround this scenario?

The first line is where it is failing. Appreciate any help!

With rRange
    .AutoFilter Field:=2, Criteria1:=projectID
    .AutoFilter Field:=3, Criteria1:="Open"
    'RangeCellcount = .SpecialCells(xlCellTypeVisible).EntireRow.Count




               For Each rActiveCell In .SpecialCells(xlCellTypeVisible)

                    If arrayCol = 12 Then
                       arrayRow = arrayRow + 1
                       arrayCol = 0
                    End If
                    If arrayRow = 3 Then
                       Exit For
                    End If

                    MyArray(arrayRow, arrayCol) = rActiveCell.Value

                    arrayCol = arrayCol + 1

                Next rActiveCell

          End With

Upvotes: 0

Views: 1859

Answers (1)

Tim Williams
Tim Williams

Reputation: 166256

You can ignore the error if there are no visible cells:

Dim rngVis as Range

With rRange 

    .AutoFilter Field:=2, Criteria1:=projectID 
    .AutoFilter Field:=3, Criteria1:="Open"

     'ignore error if no visible cells
     On Error Resume Next
     Set rngVis = .SpecialCells(xlCellTypeVisible)
     On Error Goto 0

     If Not rngVis Is Nothing then

         'work with rngVis 

     End If 'any visible cells

End With

Upvotes: 1

Related Questions