Check if range is empty (Visible Cells)

[Joker4321]

Hi I have am using an auto filter and looping through the visible cells that remain. I get an error when there is no data visible based on the filtered criteria. I have the following code...

With Sheets("DB")
Set TableRange = .Range(.Cells(Origin.Row + 3, Origin.Column + x), .Cells(End.Row - 1, Origin.Column + x))
End With

For Each First In TableRange.SpecialCells(xlCellTypeVisible)


is there a way to check if TableRange.SpecialCells(xlCellTypeVisible)
is null. Basically I want to skip the For statement if there are no visible cells after the data is filtered. Thanks a lot.

 

[smozgur]

Except EntireRow.Hidden didn't work in my special case for ListObject data range (table).

However since we always have a header, we can use following solution in my case to copy filtered data range in a table:
(tbl is a ListObject object)

If tbl.ListColumns(1).Range.SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
  'There are certainly visible cells filtered.
  tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy destinationRange
End If

 

[jimicos]

Except EntireRow.Hidden didn't work in my special case for ListObject data range (table).

However since we always have a header, we can use following solution in my case to copy filtered data range in a table:
(tbl is a ListObject object)

If tbl.ListColumns(1).Range.SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
  'There are certainly visible cells filtered.
  tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy destinationRange
End If

It appears that only the first row in the range will be evaluated when you use EntireRow.Hidden with a range of multiple rows. That's what this solution appears to be doing in my code.

 

[ericpeters]

I was also looking for an easy way to avoid the error when working with a ListObject, and found that combining the ideas of two previous posts yields a very easy solution:

If Not tbl.DataBodyRange.EntireRow.Hidden Then
'do whatever you need here with tbl.DataBodyRange.SpecialCells(xlCellTypeVisible), which is guaranteed to have at least one area in the range

 

[ericpeters]

I was also looking for an easy way to avoid the error when working with a ListObject, and found that combining the ideas of two previous posts yields a very easy solution:

If Not tbl.DataBodyRange.EntireRow.Hidden Then
'do whatever you need here with tbl.DataBodyRange.SpecialCells(xlCellTypeVisible), which is guaranteed to have at least one area in the range

Hmm, no... jimicos is right, so I changed it to this and it seems to work fine now:

 

[ericpeters]

Hmm, no... jimicos is right, so I changed it to this and it seems to work fine now:

If tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Cells.Count > 0 Then
'process visible data here

does the trick