时间戳

首页 > 解决方案 > 通过 VBA 更新 Excel 数据透视表范围获取太多行

excel - 通过 VBA 更新 Excel 数据透视表范围获取太多行

问题描述

我正在使用 Excel 2016，并且正在从 SharePoint 列表中提取数据。一旦数据被拉入，我在数据列表的开头插入了 3 行并插入了一些数据。然后我启动一个 Sub 来更新数据透视表以获取新数据并扩展表的旧范围，以便它获取所有数据。它获取所有数据，但范围超出了表本身。它最终拾取了两个不属于数据列表的额外行。如下图所示，它应该只拾取到第 11 行，但它会拾取到第 13 行。这会添加两行空白数据，从而使数据透视表的外观看起来不太好。提取范围信息的代码显示在星号之间。

数据列表示例

Sub UpdateTable()
 Dim Data_Sheet As Worksheet
 Dim Pivot_Sheet As Worksheet
 Dim StartPoint As Range
 Dim DataRange As Range
 Dim PivotName As String
 Dim PivotTables(3) As String
 Dim Table As Integer
 Dim NewRange As String
 Dim LastCol As Long
 Dim lastRow As Long

 'MsgBox Prompt:="Running UpdateTable"

 
 'Set Pivot Table & Source Worksheet
 Set Data_Sheet = ThisWorkbook.Worksheets("owssvr")
 Set Pivot_Sheet = ThisWorkbook.Worksheets("Summary")
 
 PivotTables(0) = "pvtOverOpen"
 PivotTables(1) = "pvtOverAgeBracket"
 PivotTables(2) = "pvtOverCreate"
 PivotTables(3) = "pvtOverClosed"
 'Loop through each pivot table and update it
 For Table = 0 To 3 Step 1
 'Enter in Pivot Table Name
 PivotName = PivotTables(Table)
 'Defining Staring Point & Dynamic Range
 Data_Sheet.Activate
'**********************************************************************************
 Set StartPoint = Data_Sheet.Range("A1")
 Set DataRange = Data_Sheet.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
 NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)
 'Change Pivot Table Data Source Range Address
 Pivot_Sheet.PivotTables(PivotName). _
 ChangePivotCache ActiveWorkbook. _
 PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)
'***********************************************************************************
 MsgBox Prompt:=NewRange
 'Ensure Pivot Table is Refreshed
 Pivot_Sheet.PivotTables(PivotName).RefreshTable
 'End of For Loop
 Next Table
End Sub

标签： excelvbaautomationpivot-table

解决方案

你的使用范围是多少？查看下面的链接以了解有关您实际使用范围的更多信息。

https://contexturesblog.com/archives/2012/03/01/select-actual-used-range-in-excel-sheet/

删除任何不在您预期使用范围内的行/列，保存您的工作簿，然后创建您的数据透视表。

推荐阅读