如何用VBA为关联ListObject的Chart添加随筛选自动调整的水平线？

完全可行，核心思路是基于可见行数量生成对应长度的常量值数组，并将X轴绑定可见分类范围，再配合工作表事件实现筛选后自动更新。

修改后的VBA代码实现 #

1. 核心子程序（添加/更新水平线）

Sub AddDynamicHorizontalLineToChart()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim cht As Chart
    Dim categoriesRange As Range
    Dim visibleRows As Range
    Dim lineValues() As Double
    Dim i As Integer
    Dim targetValue As Double
    Dim categoryCol As Integer
    
    ' 按需修改以下参数
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set tbl = ws.ListObjects("Table1")
    Set cht = ws.ChartObjects("Chart1").Chart
    targetValue = 0.8 ' 水平线的固定值
    categoryCol = 1 ' 分类列的索引（从1开始）
    
    ' 获取可见的分类单元格范围
    On Error Resume Next ' 处理无可见行的异常
    Set visibleRows = tbl.ListColumns(categoryCol).DataBodyRange.SpecialCells(xlVisible)
    On Error GoTo 0
    
    If visibleRows Is Nothing Then
        ' 无可见行时移除水平线（若存在）
        On Error Resume Next
        cht.SeriesCollection("水平线").Delete
        On Error GoTo 0
        Exit Sub
    End If
    
    ' 生成与可见行数量匹配的常量数组
    ReDim lineValues(1 To visibleRows.Cells.Count)
    For i = 1 To visibleRows.Cells.Count
        lineValues(i) = targetValue
    Next i
    
    ' 检查是否已存在水平线系列，存在则更新，否则新建
    Dim lineSeries As Series
    On Error Resume Next
    Set lineSeries = cht.SeriesCollection("水平线")
    On Error GoTo 0
    
    If lineSeries Is Nothing Then
        Set lineSeries = cht.SeriesCollection.NewSeries
        lineSeries.Name = "水平线"
    End If
    
    With lineSeries
        .Values = lineValues
        .XValues = visibleRows
        .ChartType = xlLine
        .AxisGroup = xlSecondary
        ' 可选：自定义线条样式，比如红色虚线
        .Format.Line.DashStyle = msoLineDash
        .Format.Line.ForeColor.RGB = RGB(255, 0, 0)
    End With
End Sub

2. 工作表事件（筛选后自动更新）

打开对应工作表的代码模块（右键工作表标签→查看代码），粘贴以下代码：

Private Sub Worksheet_Calculate()
    ' 表格筛选/计算后自动更新水平线
    AddDynamicHorizontalLineToChart
End Sub

关键说明 #

• 无需额外列：所有数值仅在VBA内存中生成，不会修改原表格结构
• 适配筛选：通过SpecialCells(xlVisible)获取可见分类行，数组长度与可见单元格数量完全匹配，保证水平线长度随筛选结果动态调整
• 自动更新：Worksheet_Calculate事件会在表格筛选、数据更新时触发，自动调用更新逻辑
• 容错处理：加入了无可见行时的异常处理，避免代码报错

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