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.

 

[royUK]

If you have no Special cells of the type selected, then an error is generated. You could trap that error, or use .Count.

What iare Origin & End, if yoare declaring them as variables then you shouldn't use words that Excel already uses such as End.

Something like

On Error GoTo nocells
For Each First In TableRange.SpecialCells(xlCellTypeVisible)
'etc
nocells: MsgBox "No rsnge available"
'or
Dim cnt As Long
cnt = TableRange.SpecialCells(xlCellTypeVisible).Cells.Count
If cnt = 0 Then Exit Sub

 

[Joker4321]

Hey Roy, the Count method was what I was looking for. I have never heard of that method before. So Count just returns the number of rows when you use it with a range?

Thanks again!

 

[Joker4321]

Sorry...turns out that I still get an error '1004' No Cells were found when the range is empty. any ideas? here is the code

DBCount as long

DBCount = TableRange.SpecialCells(xlCellTypeVisible).cells.Count

If DBCount = 0 Then
MsgBox "NO CELLS"
End If

 

[pgc01]

Hi

If you use SpecialCells(xlCellTypeVisible) on a range with no visible cells, vba will raise an error. You can use the first solution from Roy, or, for ex.

Dim rVisibleCells As Range, rCell As Range

On Error Resume Next
Set rVisibleCells = TableRange.SpecialCells(xlCellTypeVisible)
If Err > 0 Then
    MsgBox "Range was empty"
    Err.Clear
Else
    For Each rCell In rVisibleCells
    
        ' your code
    
    Next rCell
End If

Remarks:
- As Roy said you should not use a vba keyword for the name of a variable. You should rename the variable "End" to something else.

-

So Count just returns the number of rows when you use it with a range?

No, in that case with .cells.count, you would get the number of cells if there were any.
If you want the number of rows use .rows.count

Hope this helps
PGC

 

[Joker4321]

Thanks pgc...that makes sense.

 

[pgc01]

Hi again

Remark: the .rows.count only works directly in contiguous ranges.

If you are familiar with this issue disregard the rest of the post.

As you know, a range may have non-contiguous sub-ranges. Example
- TableRange is initially the rows 1:10
- you hide the rows 4:8
- you find the visible cells

Set rVisibleCells = TableRange.SpecialCells(xlCellTypeVisible)

Since the result is non-contiguous, rVisibleCells has 2 areas, the rows 1:3 and the rows 9:10.

If you use directly

rVisibleCells.rows.count

vba will by default consider the first area and gives you the result 3 (3 rows in rows 1:3)

So, in the case of a non-contiguous range, if you want to know the number of rows it spans, you may have to loop through the areas to count them.

In this basic example this simple loop would do it:

Dim rArea As Range, iAreas As Integer

For Each rArea In rVisibleCells.Areas
    iAreas = iAreas + rArea.Rows.Count
Next rArea
MsgBox "Total number of rows: " & iAreas

If you are not familiar with areas check the vba help, for ex.: "Areas Collection Object"

Hope this helps
PGC

 

[Joker4321]

Thats really helpful...this whole are is new to me but those are some good tricks. Thanks again!

 

[ZeSel]

Looks like I am 10 years late to the party, but I found that using .EntireRow.Hidden works. Once you have filtered your range, and before you try to access .SpecialCells(xlCellTypeVisible), check to see if the entire range is hidden, in conjunction with a GoTo statement.

If Range("A1:A100").EntireRow.Hidden = True Then
GoTo ContinueLoop
End If

ContinueLoop:
Next i

 

[smozgur]

If Range("A1:A100").EntireRow.Hidden = True Then

This is why I like MrExcel Message Board's policy about not marking threads as "SOLVED".

I am 20+ years VBA developer, and perhaps as I never hit Microsoft's this particular error trigger about SpecialCells somehow (which is really bad error handling by Microsoft as I always said) before, I had no idea this could have been caught like this. Amazingly neat!