VBA Excel确定是否"Table ##"已被删除.有数据 [英] VBA Excel Determine if "Table##" has data

问题描述

我开发了以下代码，以从名为"Table24"的范围创建图表.但是，有时候该表没有有效数据，然后我希望数据集的范围是一个包含0的单元格，并填充没有数据的图表.

I have the following code developed to create a chart from a named Range "Table24". However, 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.

这是5个图表中的第4个-使用调试模块，我确定这是每次运行excel文件都会崩溃的代码:

This is for the 4th out of 5 charts- using the debug module I've determined that this is the code which crashes my excel file everytime 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

我尝试过:

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

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

甚至是IS空

当表格如下所示时，我需要帮助来创建正确的参数:

I need help in creating the correct argument when the table looks like this:

作为参考，确实存在命名范围"Table24".该表中没有数据.

For reference the named range "Table24" does exist. The table has no data in it.

推荐答案

答案: 如果WorksheetFunction.CountA(Range("Table24"))= 1然后

Answer: If WorksheetFunction.CountA(Range("Table24")) = 1 Then

这篇关于VBA Excel确定是否"Table ##"已被删除.有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！