如何通过VBA在列中仅选非零值单元格区域并生成无0值柱状图

问题分析 #

原代码存在两个核心问题：

• 第8行（Set MyRange = Range(ws1.Range("X1:"), ws1.Range("X2").End(xlDown).Row).Select）语法错误，无法正确获取有效数据区域，且Set语句后不能直接使用.Select
• 直接选取整列Y:Y会包含大量空值和0值，不符合"仅展示非空非零值"的需求

修正后的完整代码 #

Sub GenerateNonZeroColumnChart()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow As Long
    Dim sourceRange As Range
    Dim xVals As Variant, yVals As Variant
    Dim filteredX() As Variant, filteredY() As Variant
    Dim i As Long, count As Long
    
    ' 定义工作表
    Set ws1 = ThisWorkbook.Worksheets("Test Summary")
    Set ws2 = ThisWorkbook.Worksheets("Graphs")
    
    ' 找到Y列最后一行有数据的行号（以Y列为准，确保X/Y数据对应）
    lastRow = ws1.Cells(ws1.Rows.Count, "Y").End(xlUp).Row
    ' 定义原始数据区域（X1到Y[lastRow]，包含表头）
    Set sourceRange = ws1.Range("X1:Y" & lastRow)
    
    ' 初始化筛选数组
    count = 0
    ReDim filteredX(1 To lastRow - 1) ' 跳过表头
    ReDim filteredY(1 To lastRow - 1)
    
    ' 遍历数据行（从第2行开始，跳过表头）
    For i = 2 To lastRow
        ' 筛选非空且非0的值
        If ws1.Cells(i, "Y").Value <> "" And ws1.Cells(i, "Y").Value <> 0 Then
            count = count + 1
            filteredX(count) = ws1.Cells(i, "X").Value
            filteredY(count) = ws1.Cells(i, "Y").Value
        End If
    Next i
    
    ' 如果没有符合条件的数据，退出子程序
    If count = 0 Then
        MsgBox "没有非空非零的数据可生成图表"
        Exit Sub
    End If
    
    ' 调整数组大小到实际有效数据量
    ReDim Preserve filteredX(1 To count)
    ReDim Preserve filteredY(1 To count)
    
    ' 在Graphs工作表创建图表
    Dim MyChart As ChartObject
    Set MyChart = ws2.ChartObjects.Add(Top:=260, Left:=815, Width:=400, Height:=250)
    
    With MyChart.Chart
        .ChartType = xlColumnClustered
        .DisplayBlanksAs = xlNotPlotted ' 确保空值不显示
        .HasLegend = False
        
        ' 添加数据系列
        With .SeriesCollection.NewSeries
            .Name = "Test"
            .XValues = filteredX ' 使用筛选后的X值数组
            .Values = filteredY ' 使用筛选后的Y值数组
            .HasDataLabels = True
        End With
    End With
End Sub

关键说明 #

1. 获取有效数据范围：通过lastRow = ws1.Cells(ws1.Rows.Count, "Y").End(xlUp).Row自动找到Y列最后一行有数据的位置，避免手动指定行号
2. 筛选非零非空值：通过遍历数据行，将Y列中不为空且不为0的对应X/Y值存入数组，确保图表只包含符合要求的数据
3. 数组赋值优势：相比直接使用单元格区域，数组筛选能彻底排除0值行，不会出现图表中留有空白位置的情况
4. 容错处理：添加了无有效数据时的提示，避免程序报错

针对你提供的第二个代码片段（chart3）的修正 #

如果要直接修改chart3的代码，同样可以用数组筛选的方式：

Sub UpdateChart3()
    Dim ws1 As Worksheet
    Dim lastRow As Long
    Dim filteredX() As Variant, filteredY() As Variant
    Dim i As Long, count As Long
    
    Set ws1 = ThisWorkbook.Worksheets("Test Summary")
    lastRow = ws1.Cells(ws1.Rows.Count, "Y").End(xlUp).Row
    
    count = 0
    ReDim filteredX(1 To lastRow - 1)
    ReDim filteredY(1 To lastRow - 1)
    
    For i = 2 To lastRow
        If ws1.Cells(i, "Y").Value <> "" And ws1.Cells(i, "Y").Value <> 0 Then
            count = count + 1
            filteredX(count) = ws1.Cells(i, "X").Value
            filteredY(count) = ws1.Cells(i, "Y").Value
        End If
    Next i
    
    If count = 0 Then
        MsgBox "没有可展示的数据"
        Exit Sub
    End If
    
    ReDim Preserve filteredX(1 To count)
    ReDim Preserve filteredY(1 To count)
    
    With chart3.Chart
        .ChartType = xlColumnClustered
        .DisplayBlanksAs = xlNotPlotted
        .HasLegend = False
        
        ' 清除原有系列（如果需要）
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
        
        With .SeriesCollection.NewSeries
            .Name = "Test"
            .XValues = filteredX
            .Values = filteredY
            .HasDataLabels = True
        End With
    End With
End Sub

内容的提问来源于stack exchange，提问作者user23357972