Excel VBA的总计数据透视表范围参考 [英] Grand total pivot table range reference for excel VBA

问题描述

我想简单地获取数据透视表总计的值.但是，我需要比在网上找到的解决方案更具动态性.

I would like to simply get the value of the grand total of a pivot table. However I need this to be more dynamic than the solution found online.

我在网上看到的一个简单解决方案是获取在合计总计列范围内找到的最后一个单元格.在这种情况下，将导致类似于下面提到的代码.

A simple solution that I have seen online was to get the last cell found in the range of the grand total column. In this case, resulting to something like the code mentioned below.

Dim grandTotal As Range
grandTotal = Range("E65536").End(xlUp)
MsgBox grandTotal.Value

但是，如果我在透视表下方的同一列中填充了一些数据，这将完全失败.有没有一种方法可以精确地引用总计价值?也许像引用两个总计列和行的数据范围，并在两者之间找到一个相交以使单元格以黄色突出显示?

However, this would simply fail if I had some data filled in below the pivot table, in the same column. Is there a way to precisely reference the grand total value? Maybe like referencing the data range of the two grand total column and row, and find an intersect between the two to get the cell highlighted in yellow?

如何获取两个不同数据值列的总计

What about getting the grand total for the two different data value columns

推荐答案

好吧，因为它是数据透视表中最右下的单元格:

Well, since it would be the most bottom right cell in your pivot table:

Set pt = Activesheet.PivotTables(1)
grandTotal = pt.DataBodyRange.Cells(pt.DataBodyRange.Cells.Count).Value

这篇关于Excel VBA的总计数据透视表范围参考的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！