Determine if range has data

user_4803331 0
Viewed 21184

The following code creates a chart from a named Range "Table24".

There will be times when this table has no valid data and then I want the range of my dataset to be a cell with 0 and populate the chart with no data.

This is for the 4th out of 5 charts- using debug I determined that this is the code which crashes my Excel file every time it's run:

'//////////////////CHART 4 Creation //////////////////////////////
Set myChtRange = ws.Range("L43:R63")
' What range contains data for chart
 
If ws.Range("Table24").Rows.Count > 0 Then
    Set myDataRange = ws.ListObjects("Table24").ListColumns(3).DataBodyRange
Else
    Set myDataRange = ws.Range("K1")
End If

' Cover chart range with chart
Set objChart = .ChartObjects.Add( _
    Left:=myChtRange.Left, Top:=myChtRange.Top, _
    Width:=myChtRange.Width, Height:=myChtRange.Height)
' Put all the right stuff in the chart
With objChart.Chart
    .ChartArea.AutoScaleFont = False
    .ChartType = xlColumnClustered
    .ChartStyle = 214
    .SetSourceData Source:=myDataRange
    .Parent.Name = "Chart4"
    .HasTitle = True
    .HasLegend = False
    .ChartTitle.Characters.Text = "Most Tolerance Holds"
    .ChartTitle.Font.Bold = True
    .ChartTitle.Font.Size = 15
  
    If ws.Range("Table24").Rows.Count > 0 Then
        .SeriesCollection(1).XValues = ws.ListObjects("Table24").ListColumns(2).DataBodyRange
    Else
        .SeriesCollection(1).XValues = ws.Range("K1")
    End If

    With .Axes(xlCategory, xlPrimary)
        .HasTitle = True
        With .AxisTitle
            .Characters.Text = " "
            .Font.Size = 10
            .Font.Bold = True
        End With
    End With
    With .Axes(xlValue, xlPrimary)
        .HasTitle = True
        .DisplayUnit = none
        .HasDisplayUnitLabel = False
        .TickLabels.NumberFormat = "#,##0.0"
        With .AxisTitle
            .Characters.Text = "Lines"
            .Font.Size = 15
            .Font.Bold = True
        End With
    End With
End With

I tried:

If ws.ListObjects("Table24").DataBodyRange.Rows.Count > 0 Then

If NOT ws.ListObjects("Table24").DataBodyRange Is Nothing Then

And even the IS Empty

I need help creating the argument when the table looks like this:
enter image description here

The named range "Table24" does exist. The table has no data in it.
enter image description here

vba excel
5 Answers

The databodyrange property of a listobject represents the range of data. if there is no data in the list object, then the range has nothing

if ws.ListObjects("Table24").DataBodyRange is Nothing then
  'Do something if there is no data
Else
  'Do something if there is data
end if
user_90341040

I know this is old but for anyone reading this, the correct way to check for an empty table is using ListRows.Count.

This will give zero if the table is empty. In other words, if you delete all the rows in a table then ListRows.Count will be zero.

If Sheet1.ListObjects("Table").ListRows.Count = 0 Then
    ' empty
Else
    ' not empty
End If
user_44741950

I'll share the function I use. It returns TRUE if a ListObject (table) is empty below the header row and above total row. (It still works if the table isn't displaying either the header or total row.)

This function checks for 1 of 4 possibilities:

  1. If the ListObject (table) variable "Is Nothing" it generates an easy to understand error.
  2. If the table doesn't have a DataBodyRange (no data was added below the header and above the total row and no blank rows were added to the table except for the row automatically created when the table was created) then it returns False.
  3. If the table has a DataBodyRange but every cell below the header and above the total row is Empty (does not contain a value, not even a zero length vbNullString character) then it returns False.
  4. If (a) the table has a DataBodyRange and (b) at least one cell below the header and above the total row is NOT Empty (contains any value, including a zero length vbNullString character) then it returns False.

Function IsEmptyTable()

Public Function IsEmptyTable(ByVal target As Excel.ListObject) As Boolean        

    Const APPNAME As String = "IsEmptyTable()"

    If target Is Nothing Then
        ' The parameter doesn't contain a ListObject (table).
        Err.Raise 2310, _
                  APPNAME, _
                  APPNAME & String$(2, vbNewLine) & "Parameter 'target' must refer to a valid ListObject (table)."

    ElseIf target.DataBodyRange Is Nothing Then
        ' The table doesn't have a databody range and therefore has no data below
        '   the header row and above the total row.
        IsEmptyTable = True

    Else
        ' The table has a DataBodyRange.
        '   Check it for any non-empty cells (including zero-length
        '   vbNullString).  Return TRUE if any are found.
        IsEmptyTable = Excel.WorksheetFunction.CountA(target.DataBodyRange) = 0

    End If
End Function
user_56403420
Related