How do I check if a range of SpecialCells contains cells?

eirikdaude 0
Viewed 20

I ran into an edge case in one of my spreadsheets recently. I have a loop going over all visible cells in a table:

For Each c In ws.ListObjects(1).ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)
    ...
Next c

Normally there will always be a few items visible, but in an edge case none were, which generated an error in my code:

Run-time error '1004': No cells were found

I tried wrapping the for-loop in an if-statement

If ws.ListObjects(1).ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Count > 0 Then

But this generates the same error. Similarly if I check if the range is nothing.

How do I go about checking if there are visible cells in the filtered table?

vba excel
2 Answers

As a temporary solution, I used the solution shown below, where On Error Resume Next causes the error to be skipped, allowing me to check if a range has been assigned to the variable rng afterwards.

Dim rng As Range
Dim c As Range
On Error Resume Next
Set rng = ws.ListObjects(1).ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then
    For Each c In rng
        ...
    Next c
End If

I suppose a better solution might be creating a proper error handler for the function, as the current implementation is kinda ugly...

eirikdaude0

I would use a helper function to which you pass the listobject

Private Function hasVisibleRows(lo As ListObject) As Boolean

If lo.DataBodyRange Is Nothing Then Exit Function

On Error Resume Next
hasVisibleRows = lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Count > 0
On Error GoTo 0

End Function

This is one of the rare cases where on error resume next is valid, as there is no other way to check for visible rows.

But I would always put the check in a separate function.

useryPy710
Related