如何实现数据透视表动态数据捕获 调整VBA代码适配不同数据集

核心思路 #

替换原代码里硬编码的固定行、列范围，提前动态获取Sheet1中有效数据的最大行号、最大列号，再拼接成数据源地址即可。推荐使用边界反向查找的方式获取范围，比直接调用UsedRange准确性更高，不会误包含之前编辑过、后来清空的空白单元格。

完整修改后代码 #

Dim maxRow As Long, maxCol As Long
Dim sourceAddr As String

' 读取Sheet1的有效数据范围
With Sheets("Sheet1")
    ' 从A列最后一行向上查找，获取最后一条有数据的行号
    maxRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    ' 从首行最后一列向左查找，获取最后一个有数据的列号
    maxCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    ' 拼接R1C1格式的动态数据源地址
    sourceAddr = "Sheet1!R1C1:R" & maxRow & "C" & maxCol
End With

' 创建透视表逻辑保持不变，仅替换数据源参数
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
sourceAddr, Version:=6).CreatePivotTable TableDestination:= _
"Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6

特殊场景适配 #

• 如果你的数据首列存在空白行，可改用UsedRange获取最大行：

maxRow = Sheets("Sheet1").UsedRange.Rows.Count

• 如果担心用户手动修改工作表名称导致代码失效，可以把Sheets("Sheet1")替换为工作表的CodeName（对应VBA编辑器工程窗口显示的工作表名称）。

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