VBA Pivot Table Ranges: How to Set and Resize Them

VBA & macros · Updated November 18, 2026

In VBA, a PivotTable’s displayed range is read with PivotTable.TableRange2, while its source data range is set through the PivotCache when you create or change the PivotTable. These are different ranges: one is the report on the worksheet, and the other is the data Excel summarizes.

What “PivotTable range” means

A PivotTable involves at least two ranges that are easy to confuse. The source range contains the records and field headings used to build the PivotTable. The report range is the area occupied by the PivotTable on its worksheet. Changing one does not automatically mean you have changed the other.

For an existing PivotTable, TableRange2 returns the complete report range, including any report-filter fields above the main body. TableRange1 returns the report range without those page fields. Both properties are useful for checking where a PivotTable currently extends, selecting or formatting its output, or avoiding an overwrite when a macro writes nearby.

The main report-range properties are read-only: they tell you where the PivotTable is, but you do not resize the report by assigning a new address to them. The displayed dimensions can change when you refresh, expand or collapse fields, or change the layout. If you need to control where a new PivotTable starts, specify its destination when creating it.

Read the displayed range in VBA

Use the worksheet’s PivotTables collection to identify a PivotTable, then read its range. The example below shows the full report address, including the workbook and worksheet. Change the sheet and PivotTable names to match your file.

Sub ShowPivotTableRange()
    Dim ws As Worksheet
    Dim pt As PivotTable

    ' Identify the worksheet and existing PivotTable.
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("SalesPivot")

    ' Display the complete report range, including report filters.
    MsgBox pt.TableRange2.Address(External:=True)
End Sub

For example, the message might show a reference such as [Report.xlsm]Summary!$A$3:$F$18. That is the PivotTable’s current output footprint, not its original source data. A PivotTable with no report-filter fields may have the same TableRange1 and TableRange2 addresses.

For the values area alone, use DataBodyRange. It excludes row and column labels, which is helpful when a macro should process only summarized values. Check that the property returns a range before using it: a PivotTable with no value fields may not have a data body to work with.

Set the source range when creating a PivotTable

A PivotTable is built from a PivotCache, which stores or connects to the data used for analysis. In VBA, the usual sequence is to identify the source data, create a cache, and create a PivotTable at a destination cell. The following example uses the contiguous block beginning at A1 on a data sheet and places a new PivotTable at A3 on a report sheet.

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z. Make sure the source has one unique, nonblank heading per column, the destination is clear, and the proposed PivotTable name is not already in use.

Sub CreateSalesPivot()
    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim sourceRange As Range
    Dim destination As Range
    Dim cache As PivotCache
    Dim pt As PivotTable

    Set wb = ThisWorkbook
    Set wsData = wb.Worksheets("Data")
    Set wsReport = wb.Worksheets("Summary")
    Set sourceRange = wsData.Range("A1").CurrentRegion
    Set destination = wsReport.Range("A3")

    ' Pass the source address as text to create the cache.
    Set cache = wb.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=sourceRange.Address( _
            ReferenceStyle:=xlR1C1, External:=True))

    ' Create the report at the chosen starting cell.
    Set pt = cache.CreatePivotTable( _
        TableDestination:=destination, _
        TableName:="SalesPivot")
End Sub

This creates the PivotTable structure, but it does not add row, column, or value fields. Those are configured separately through the PivotTable’s PivotFields collection. CurrentRegion is convenient when the data is a solid block, but a blank row or column can split the region. Check the selected range before relying on it in a workbook where records may have gaps.

Use an Excel table for a changing source

A fixed source address, such as Data!$A$1:$D$200, does not automatically include records added below row 200. That is a common reason a PivotTable appears to ignore new data. An Excel table is often a better source for a regularly updated list because the table expands as records are added.

On Windows, create one by selecting the data and choosing Insert > Table, then confirm that the range has headers. Give the table a clear name in Table Design > Table Name. A PivotCache can use the table name as its source, for example SalesData. After adding rows, refresh the PivotTable so it incorporates the latest table contents; the table’s expansion does not itself refresh the report.

If you already have a PivotTable and need to point it at a different source, the worksheet interface offers PivotTable Analyze > Change Data Source. In VBA, an existing PivotTable can be switched to a newly created cache with its ChangePivotCache method, where the source type supports that operation. Changing sources deserves care: field names must match the fields your code or report layout expects. For external data and some specialized PivotTables, the available source-change options differ.

Refresh and recheck after changes

Refreshing updates a PivotTable from its cache; it does not necessarily repair a source range that omits newly appended records. First confirm that the cache points to the intended data, then refresh. In Excel for Microsoft 365 on Windows, use Data > Refresh All to refresh workbook connections and reports, or use PivotTable Analyze > Refresh for the selected PivotTable.

A refresh can change the report’s dimensions. For example, adding a new category may add a row, while removing categories may shorten the report. Consequently, a range address saved before a refresh can become stale. Read TableRange2 again after refreshing if later code depends on the current footprint.

In VBA, a PivotTable’s RefreshTable method refreshes that report and returns a Boolean indicating whether the refresh succeeded. When a workbook has several PivotTables using a shared cache, refreshing one may update other reports connected to that cache too. Avoid assuming that each PivotTable has an entirely separate copy of its source.

Choose the right range property

Use the property that matches the task rather than treating every PivotTable-related range as interchangeable:

  • TableRange2: the entire displayed report, including report-filter fields. Use it to find the full area occupied by the PivotTable.
  • TableRange1: the displayed report excluding report-filter fields. Use it when those fields above the report should not be included.
  • DataBodyRange: the summarized values area. Use it when the task concerns values rather than labels.
  • RowRange or ColumnRange: the row-label or column-label area, respectively, when code needs to address those parts of the layout.
  • The PivotCache source: the records used for the analysis. Define or change this through the cache and source-data workflow, not by assigning a new address to TableRange2.

For example, if you want to apply formatting to all PivotTable output, TableRange2 is usually the natural choice. If you want to copy just the numbers, use DataBodyRange after checking that it exists. If you want to include new source records, expand or redefine the source and refresh; selecting the displayed report range will not do that.

Common range problems and their causes

  • New records are missing: the source may be a fixed address that ends above the new rows. Extend the source or use an Excel table, then refresh.
  • The detected range stops early: CurrentRegion stops at blank rows and columns. Remove unintended gaps or specify the intended range explicitly.
  • The PivotTable overwrites nearby content: its output can grow after a refresh or layout change. Leave space around the report or check its current TableRange2 before writing adjacent content.
  • VBA cannot find a named PivotTable: verify the worksheet name and the PivotTable’s actual name. These are separate names; a sheet’s tab name is not its PivotTable name.
  • Creation fails because of field headings: source headers should be nonblank and unique. Duplicate or empty headings can prevent Excel from creating a valid PivotTable.
  • A displayed range is mistaken for the source: TableRange2 describes the report, not the records behind it. Inspect and update the source separately.

Windows, Mac, and Excel for the web

The VBA object model examples apply to desktop Excel for Microsoft 365 on Windows. Excel for Mac also supports VBA, though the Visual Basic Editor access and some interface details can differ by keyboard and version. On Windows, Alt+F11 opens the Visual Basic Editor; on Mac, the shortcut may be Option+F11 or require the Fn key, depending on the keyboard. The Developer tab’s Visual Basic command is another route when that tab is enabled.

Excel for the web does not run VBA macros. You can open and work with PivotTables there, but run VBA in desktop Excel. Macro availability can also be affected by workbook format and security settings; a macro-enabled workbook is normally saved as .xlsm, and macros should only be enabled for files you trust.

Frequently Asked Questions

How do I get the range of a PivotTable in VBA?
Set a PivotTable object, then read pt.TableRange2.Address for the complete report range. Use pt.TableRange1.Address to exclude report-filter fields.

Can I assign a new address to TableRange2?
No. TableRange2 is a read-only description of the current report footprint. To choose a starting location, set TableDestination when creating the PivotTable; to change its source, work with the PivotCache.

How do I expand a PivotTable’s source range with VBA?
Use an Excel table as the source when records are routinely appended, or create a cache from the expanded source range. Then refresh the PivotTable. Expanding the report range itself does not add source records.

What is the difference between TableRange1 and TableRange2?
TableRange2 includes report-filter fields, while TableRange1 excludes them. If there are no report-filter fields, the two ranges may be identical.

Why does CurrentRegion miss some rows?
CurrentRegion treats a blank row or column as a boundary. If the data contains gaps, define the source explicitly or organize it as an Excel table before building the PivotTable.

Does refreshing change the PivotTable range?
It can. New or removed items may make the report taller or shorter, so retrieve TableRange2 again after the refresh instead of relying on an old address.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.