Excel多工作表批量创建堆叠柱状图：动态范围与循环选表问题求助

问题背景 #

每月处理多份绩效Excel文件，合并后按岗位拆分工作表（隐藏非本岗位员工数据），需要为每个岗位工作表生成堆叠柱状图，但遇到两个核心问题：

• 原代码无法仅统计可见单元格的行数，导致图表会包含隐藏的员工数据
• For Each WS循环依赖ActiveSheet，没有正确关联目标工作表，容易出现运行错误

原代码 #

SUB CHARTS()

DIM LR As Long

Sheets("A").Select  
Dim ALR As Long
With ActiveSheet
ALR = Cells(Rows.Count, 1).End(xlUp).Row
End with
Sheets("B").Select
Dim BLR As Long
With ActiveSheet
ALR = Cells(Rows.Count, 1).End(xlUp).Row
End With

For Each WS in ThisWorkbook.Worksheets
IF(WS.Name ="A") THEN
LR = ALR
End IF
IF (WS.Name ="B") THEN
LR = BLR
End IF

With ActiveSheet.ChartObjects.Add _
(Left:=850, Width:=1536, Top:=0, Height:=864)
.Chart.ChartType = xlColumnStacked
End With

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = "=" & ActiveSheet.Name & "! $EY$1"
ActiveChart.FullSeriesCollection(1).Values = "=" & ActiveSheet.Name & "!$EY$2:$EY$" & LR

NEXT WS

优化后的代码 #

Sub CreatePerformanceCharts()
    Dim ws As Worksheet
    Dim lastVisibleRow As Long
    Dim chartObj As ChartObject
    Dim visibleCells As Range
    
    ' 遍历工作簿内所有工作表
    For Each ws In ThisWorkbook.Worksheets
        ' 只处理目标岗位工作表（可根据实际岗位名调整判断条件）
        If ws.Name = "A" Or ws.Name = "B" Then
            ' 获取列A的所有可见单元格
            Set visibleCells = ws.Columns(1).SpecialCells(xlCellTypeVisible)
            ' 取最后一个可见单元格的行号
            lastVisibleRow = visibleCells(visibleCells.Count).Row
            
            ' 在当前工作表创建堆叠柱状图
            Set chartObj = ws.ChartObjects.Add(Left:=850, Width:=1536, Top:=0, Height:=864)
            With chartObj.Chart
                .ChartType = xlColumnStacked
                ' 添加绩效指标系列
                With .SeriesCollection.NewSeries
                    .Name = "='" & ws.Name & "'!$EY$1"
                    .Values = "='" & ws.Name & "'!$EY$2:$EY$" & lastVisibleRow
                    ' 绑定横轴员工姓名（假设姓名在列A）
                    .XValues = "='" & ws.Name & "'!$A$2:$A$" & lastVisibleRow
                End With
            End With
        End If
    Next ws
End Sub

核心优化说明 #

1. 修正工作表循环问题

   • 彻底移除Select和Activate操作，直接通过ws对象操作目标工作表，避免因激活状态变化导致的错误
   • For Each ws循环会逐个遍历工作表，代码中所有操作都绑定到ws对象，无需手动选中工作表

2. 统计可见单元格行数

   • 使用SpecialCells(xlCellTypeVisible)筛选出列A的所有可见单元格，再取最后一个单元格的行号，确保只统计显示的员工数据
   • 处理了列A存在多个不连续可见区域的情况，保证取到的是最后一行可见数据

3. 代码稳定性与可读性提升

   • 为工作表名添加单引号（'），避免工作表名含空格或特殊字符时公式报错
   • 通过对象变量（chartObj）直接操作图表，无需激活图表，减少运行时的不稳定因素
   • 变量命名更直观，便于后续维护扩展

4. 扩展性调整

   • 如果有10类岗位，可把If ws.Name = "A" Or ws.Name = "B"改成更灵活的判断，比如If Left(ws.Name, 2) = "岗位"（根据实际命名规则调整），实现批量处理所有岗位工作表

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