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:
The named range "Table24" does exist. The table has no data in it.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With