VBA to match number of table rows to pivot rows?

mcgrooty

New Member
Joined
Feb 5, 2014
Messages
21
Office Version
  1. 365
Platform
  1. Windows
I'm trying to get VBA to get a table in my worksheet to match the number of rows in a pivot in the same worksheet; I searched online and found the following code:

VBA in module:

Sub ResizeTable()
Dim pt As PivotTable
Dim tbl As ListObject

Set pt = ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1")
Set tbl = ThisWorkbook.Sheets("Pivot").ListObjects("Table1")

tbl.Resize tbl.Range.Resize(pt.TableRange2.Rows.Count, tbl.ListColumns.Count)

End Sub

VBA in worksheet:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim tbl As ListObject
Set tbl = Me.ListObjects("Table1") ' Change "Table1" to your table name

' Resize the table based on the pivot table size
tbl.Resize tbl.Range.Resize(Target.TableRange2.Rows.Count, tbl.ListColumns.Count)
End Sub

It works, except it adds two rows too many to the table, such that with the subtotal it ends up being two rows past the pivot...not the end of the world but I'd like to get it matching and to understand why it wasn't...
 

Attachments

  • tablepivot.JPG
    tablepivot.JPG
    215.4 KB · Views: 5

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Now learning that the VBA in the module is redundant, so please ignore that
 
Upvote 0
So, I tried this - added the "-1" after Target.TableRange2.Rows.Count:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim tbl As ListObject
Set tbl = Me.ListObjects("Table1") ' Change "Table1" to your table name

' Resize the table based on the pivot table size
tbl.Resize tbl.Range.Resize(Target.TableRange2.Rows.Count - 1, tbl.ListColumns.Count)
End Sub

It's an improvement, but here is the actual behaviour: if the data returned is more than what was already in the pivot, and the pivot has to add rows, the table will match; however if the data returned is less than what was already in the pivot, and the pivot has to reduce rows, when the table is reduced it still has one row too many...
 
Upvote 0
Assuming that Forecast is the heading of the table, try:
Rich (BB code):
tbl.Resize tbl.Range.Resize(Target.DataBodyRange.Rows.Count, tbl.ListColumns.Count)
 
Last edited:
Upvote 0
Assuming that Forecast is the heading of the table, try:
Rich (BB code):
tbl.Resize tbl.Range.Resize(Target.DataBodyRange.Rows.Count, tbl.ListColumns.Count)
Thank you for the suggestion, Alex - I tried it, and it has the reverse behaviour of the previous code; if upon refresh the pivot table has fewer rows, the table reduces to match; if however the pivot table has added rows, the table will add rows, but will be one short
 

Attachments

  • OneShort.JPG
    OneShort.JPG
    132 KB · Views: 1
Upvote 0
Having the Totals Row seems to be causing it to have inconsistent results between increasing and decreasing the size of the table.
Give this a try, it toggles the totals row on and off.

VBA Code:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim tbl As ListObject
    Set tbl = Me.ListObjects("Table2") ' Change "Table1" to your table name
   
    tbl.ShowTotals = False
    tbl.Resize tbl.Range.Resize(Target.DataBodyRange.Rows.Count, tbl.ListColumns.Count)
    tbl.ShowTotals = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,312
Messages
6,177,812
Members
452,806
Latest member
Workerl3ee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top