VBA Determine if Table body is empty

[Chris Macro]

Is there an easy way to determine if the "DataBodyRange" of a table is empty? I want to use some if/then statements to create some error messages if there is no data in a specific table. Thanks in advance for the help!

 

[Gary McMaster]

Maybe "SpecialCells"?

Something like this:

Public Sub Test()

Dim oBlank As Range
Dim oTestRange As Range
Dim oSheet As Worksheet

Set oSheet = ActiveSheet 'Adjust sheet to suit
Set oTestRange = oSheet.Range("A1:F30") 'Adjust range to suit

Set oBlank = oTestRange.SpecialCells(xlCellTypeBlanks)

If oBlank.Cells.Count = oTestRange.Cells.Count Then
    MsgBox "Specified range is empty"
Else
    MsgBox "Specified range is NOT empty"
End If

End Sub

 

[Audamus]

Maybe "SpecialCells"?

Something like this:

Public Sub Test()

Dim oBlank As Range
Dim oTestRange As Range
Dim oSheet As Worksheet

Set oSheet = ActiveSheet 'Adjust sheet to suit
Set oTestRange = oSheet.Range("A1:F30") 'Adjust range to suit

Set oBlank = oTestRange.SpecialCells(xlCellTypeBlanks)

If oBlank.Cells.Count = oTestRange.Cells.Count Then
    MsgBox "Specified range is empty"
Else
    MsgBox "Specified range is NOT empty"
End If

End Sub

This worked for me:

Public Function CheckTbl(tbl As ListObject) As Boolean
With tbl
Dim bempty As Boolean
'Check if table is empty
If .DataBodyRange Is Nothing Then
bempty = True
Else
'Table must not be empty
bempty = False
End If
End With
CheckTbl = bempty
End Function

 

[CubaRJ]

This worked for me:

Public Function CheckTbl(tbl As ListObject) As Boolean
With tbl
Dim bempty As Boolean
'Check if table is empty
If .DataBodyRange Is Nothing Then
bempty = True
Else
'Table must not be empty
bempty = False
End If
End With
CheckTbl = bempty
End Function

Hi, this does not work when the .DatabodyRange is empty and return error 91.