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?
CodePudding user response:
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...
CodePudding user response:
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.