Bottom line: Learn how to add buttons to your pivot tables and pivot charts to quickly expand/collapse entire fields using a VBA macro.
Skill level: Intermediate
Expanding & Collapsing Fields is Time Consuming
Expanding and collapsing entire fields in a pivot table can be a multi-step process that is a bit time consuming. This is especially true for the users/consumers of your Excel report files that are not familiar with the process.
To expand or collapse the ENTIRE field, we tend to press the little expand (+) and collapse (-) field buttons for each item in the field.
There are shortcuts for this on the right-click menu and the Options/Analyze tab in the ribbon.
We can also press the Expand Field and Collapse Field buttons on the Analyze/Options tab of the ribbon. A cell inside the Rows or Columns area of the pivot table must be selected for these buttons to work (be enabled).
Keyboard shortcuts to Expand/Collapse fields:
Expand Entire Field: Alt, A, J Or Menu Key, E, E
Collapse Entire Field: Alt, A, H Or Menu Key, E, C
Note: A cell in the Rows or Columns area must be selected for the keyboard shortcuts to work.
However, there are no dedicated buttons on the pivot table to expand/collapse the entire field.
New Expand Collapse Buttons in Excel 2016 Pivot Charts
In Excel 2016 a new feature was added to pivot charts that allows us to expand or collapse the fields in the Rows area. You will see little plus and minus button in the bottom right corner of your pivot chart.
However, this feature is ONLY available in Excel 2016 (Office 365). So, I created a few macros that will work on any version of Excel, in both the Rows and Columns area of a pivot table or pivot chart.
We can also modify the pivot chart to just display the expand/collapse buttons, and add them above the pivot table. Here is an animation that shows how to do that.
The steps to modify the pivot chart are:
- Delete the Values and Axis field buttons on the chart.
- Delete the axis labels, title, and legend.
- Resize the chart to only display the Expand/Collapse buttons.
The chart will be hidden behind the buttons. So we are just using the pivot chart for the expand/collapse buttons. It's important to note that this will only work for fields in the Rows area of the pivot table, and only in Excel 2016 (Office 365).
Expand Collapse Entire Pivot Fields Macros
This set of macros will expand or collapse the NEXT field in the rows or columns area. This allows us to drill down to show the details of the fields in the Rows or Columns Area.
A good example is when the pivot table has fields in the rows area for Year, Quarter, Month, Day. We might want to compare year totals, then drill down to see totals by quarter or month.
If the pivot table is currently collapsed to years, the “Expand_Entire_RowField” macro will expand ALL of the Year items to display the Quarters for each year.
Running the “Collapse_Entire_RowField” macro will collapse the Year items back.
You can download the file that contains the macros below.
Assign the Macros to Shapes or Buttons
We can assign these macros to shapes or buttons on the sheet.
The buttons can be continually pressed to drill up or down through the fields. This makes it easy for users of your reports to quickly expand or collapse entire fields.
The shapes can be pasted inside a pivot chart to mimic the functionality of the expand/collapse buttons in Excel 2016.
The VBA Macro Code
There are four different macros for the expand/collapse on the Rows and Columns areas. The macros use For Next Loops to do some pretty aggressive looping through the pivot fields and items.
The Expand macro finds the field in the highest position that is collapsed by checking the ShowDetail property of each PivotItem. If it does NOT find an expanded item, then it expands the entire field. If all items are expanded, then it proceeds to the field in the next position down.
Sub Expand_Entire_RowField()
'Expand the lowest position field in the Rows area
'that is currently expanded (showing details)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long
'Create reference to 1st pivot table on sheet
'Can be changed to reference a specific sheet or pivot table.
Set pt = ActiveSheet.PivotTables(1)
'Count fields in Rows area minus 1 (last field can't be expanded)
iFieldCount = pt.RowFields.Count - 1
'Loop by position of field
For iPosition = 1 To iFieldCount
'Loop fields in Rows area
For Each pf In pt.RowFields
'If position matches first loop variable then
If pf.Position = iPosition Then
'Loop each pivot item
For Each pi In pf.PivotItems
'If pivot item is collapsed then
If pi.ShowDetail = False Then
'Expand entire field
pf.ShowDetail = True
'Exit the macro
Exit Sub
End If
Next pi
End If
Next pf
'If the Exit Sub line is not hit then the
'loop will continue to the next field position
Next iPosition
End Sub
The Collapse macro does the opposite. It starts at the lowest field position and works backwards until it finds a pivot item that is NOT collapsed. If it finds an expanded item then it collapses the entire field. Otherwise it moves up to the field in the next position in the Rows area, and repeats the process.
Sub Collapse_Entire_RowField()
'Collapse the lowest position field in the Rows area
'that is currently expanded (showing details)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long
'Create reference to 1st pivot table on sheet
'Can be changed to reference a specific sheet or pivot table.
Set pt = ActiveSheet.PivotTables(1)
'Count fields in Rows area minus 1 (last field can't be expanded)
iFieldCount = pt.RowFields.Count - 1
'Loop backwards by position of field (step -1)
For iPosition = iFieldCount To 1 Step -1
'Loop fields in Rows area
For Each pf In pt.RowFields
'If position matches first loop variable then
If pf.Position = iPosition Then
'Loop each pivot item
For Each pi In pf.PivotItems
'If pivot item is expanded then
If pi.ShowDetail = True Then
'Collapse entire field
pf.ShowDetail = False
'Exit the macro
Exit Sub
End If
Next pi
End If
Next pf
'If the Exit Sub line is not hit then the
'loop will continue to the next field position
Next iPosition
End Sub
Download the File
You can download the file that contains the macros for the Rows and Columns areas.
How Can We Improve It?
The Expand/Collapse Macro buttons should make a nice addition to any report file you send to users that aren't Excel ninjas. I've seen many users click the small expand/collapse buttons repeatedly for each pivot item, simply because they didn't know there was a better way. This macro should help give them a faster and easier way to drill down through your pivot tables or charts.
Please leave a comment below with questions or suggestions on how we can improve this. Thanks so much! 🙂
Great!
Thank you Jon
Thanks for your support Hocine! 🙂
Hi Jon. I like your idea with the macro very much but it doesn’t seem to work for me.
Is it only for Office 2016?
I’m using 2010.
Hi Volodymyr,
I believe it should work in 2010, but haven’t tested. Are you getting an error with the example file you can download above?
Great job…I really apreciate it.
Thanks Thanasis! 🙂
Hi Jon,
I didn’t realize Pivot Charts in Excel 2016 had those buttons. Thanks for creating this! Did someone ask you for this or did you think it would be fun to create?
Cheers,
Kevin
http://www.myspreadsheetlab.com/blog/
Thanks Kevin! 🙂 This macro was inspired by a different question about groups, which I’ll cover in a future post.
We can also use those pivot chart buttons in place of the macro buttons if your groups are in the Rows area. I added a screencast animation in the section on PivotCharts above to show what that looks like.
A lot of thanks Jon
Thanks Irfan! 🙂
Hi Jon,
For some reason the macro results in an error type: “Type Mismatch” at the following command line:
” If pi.ShowDetail = False Then”
and when verifying the content of the variable pi in
” For Each pi In pf.PivotItems
the value contained in pi is “<05/01/2015" could it be because my date setting on my desktop being "dd-mm-yyyy"?
Just wondering. I am actually using Excel 2010.
I’ll get the same error but only after changing the pivot table configuration.
Under certain conditions pi.ShowDetail errors when pi.name equals “<05/01/2015".
@Jon,
are there any requirements?
Only after changes to the pivot table I’ll get the same error.
pi.ShowDetail errors when pi.Name equals “<05/01/2015"
@Jon,
are there any requirements?
Excellent macro Jon, both functional and educational. I am enjoying your course so much, and learning every time I take another lesson module.
I am looking forward to using your training to get some of my spreadsheets functioning in a way more efficient manner!
Thank you so much Nigel! I’m excited to have you as a member of The VBA Pro Course, and happy to hear you are learning more about VBA. 🙂
Jon:
How is the date hierarchy automatically detected without a supporting date dimension to provide the added date intelligence for the roll-up & drill-down?
GW
Hi George,
Great question! The hierarchy is created with the Group feature of the pivot table. Here is an article that explains the Date Grouping in a Pivot Table versus using formulas. I hope that helps. Thanks! 🙂
Jon:
Thanks. Proves that we all
Jon:
Thanks. Proves that we all have lots to learn. I am looking forward to continuing the lifelong quest for continuous learning!
GW
Jon:
Thanks. Proves that we all have lots to learn. I am looking forward to continuing the lifelong quest for continuous learning!
George
Jon,
Sorry I think I uploaded the wrong macros. Here below is the adjustment I made to the macro not to get any more the Type mismatch error 13. This will expand all or collapse all rows or columns:
Option Explicit
Sub Expand_Entire_RowField()
‘Expand the lowest position field in the Rows area
‘that is currently expanded (showing details)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long
‘Create reference to 1st pivot table on sheet
‘Can be changed to reference a specific sheet or pivot table.
Set pt = ActiveSheet.PivotTables(1)
‘Count fields in Rows area minus 1 (last field can’t be expanded)
iFieldCount = pt.RowFields.Count – 1
‘Loop by position of field
For iPosition = 1 To iFieldCount
‘Loop fields in Rows area
For Each pf In pt.RowFields
‘If position matches first loop variable then
If pf.Position = iPosition Then
‘Loop each pivot item
For Each pi In pf.PivotItems
‘If pivot item is collapsed then
ActiveSheet.PivotTables(1).PivotFields(“Years”).ShowDetail = True
ActiveSheet.PivotTables(1).PivotFields(“Quarters”).ShowDetail = True
ActiveSheet.PivotTables(1).PivotFields(“Months”).ShowDetail = True
‘ If pi.ShowDetail = False Then
‘Expand entire field
‘ pf.ShowDetail = True
‘Exit the macro
Exit Sub
‘ End If
Next pi
End If
Next pf
‘If the Exit Sub line is not hit then the
‘loop will continue to the next field position
Next iPosition
‘ Set pt = ActiveSheet.PivotTables(1)
‘
‘ iFieldCount = pt.RowFields.Count – 1
‘
‘ For iPosition = 1 To iFieldCount
‘ For Each pf In pt.RowFields
‘ If pf.Position = iPosition Then
‘ For Each pi In pf.PivotItems
‘ If pi.ShowDetail = False Then
‘ pf.ShowDetail = True
‘ Exit Sub
‘ End If
‘ Next pi
‘ End If
‘ Next pf
‘ Next iPosition
End Sub
Sub Collapse_Entire_RowField()
‘Collapse the lowest position field in the Rows area
‘that is currently expanded (showing details)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long
‘Create reference to 1st pivot table on sheet
‘Can be changed to reference a specific sheet or pivot table.
Set pt = ActiveSheet.PivotTables(1)
‘Count fields in Rows area minus 1 (last field can’t be expanded)
iFieldCount = pt.RowFields.Count – 1
‘Loop backwards by position of field (step -1)
For iPosition = iFieldCount To 1 Step -1
‘Loop fields in Rows area
For Each pf In pt.RowFields
‘If position matches first loop variable then
If pf.Position = iPosition Then
‘Loop each pivot item
For Each pi In pf.PivotItems
‘If pivot item is expanded then
ActiveSheet.PivotTables(1).PivotFields(“Months”).ShowDetail = False
ActiveSheet.PivotTables(1).PivotFields(“Quarters”).ShowDetail = False
ActiveSheet.PivotTables(1).PivotFields(“Years”).ShowDetail = False
‘ If pi.ShowDetail = True Then
‘Collapse entire field
‘ pf.ShowDetail = False
‘Exit the macro
Exit Sub
‘ End If
Next pi
End If
Next pf
‘If the Exit Sub line is not hit then the
‘loop will continue to the next field position
Next iPosition
End Sub
Sub Expand_Entire_ColumnField()
‘Expand the lowest position field in the Columns area
‘that is currently expanded (showing details)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long
Set pt = ActiveSheet.PivotTables(1)
iFieldCount = pt.ColumnFields.Count – 1
For iPosition = 1 To iFieldCount
For Each pf In pt.ColumnFields
If pf.Position = iPosition Then
For Each pi In pf.PivotItems
ActiveSheet.PivotTables(1).PivotFields(“Years”).ShowDetail = True
ActiveSheet.PivotTables(1).PivotFields(“Quarters”).ShowDetail = True
‘ If pi.ShowDetail = False Then
‘ pf.ShowDetail = True
Exit Sub
‘ End If
Next pi
End If
Next pf
Next iPosition
End Sub
Sub Collapse_Entire_ColumnField()
‘Collapse the lowest position field in the Columns area
‘that is currently expanded (showing details)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long
‘Start at bottom
‘Loop to find if any datarange cells show details
‘if no, go to next field,
‘if yes, collapse entire field
Set pt = ActiveSheet.PivotTables(1)
iFieldCount = pt.ColumnFields.Count – 1
For iPosition = iFieldCount To 1 Step -1
For Each pf In pt.ColumnFields
If pf.Position = iPosition Then
For Each pi In pf.PivotItems
ActiveSheet.PivotTables(1).PivotFields(“Quarters”).ShowDetail = False
ActiveSheet.PivotTables(1).PivotFields(“Years”).ShowDetail = False
‘ If pi.ShowDetail = True Then
‘ pf.ShowDetail = False
Exit Sub
‘ End If
Next pi
End If
Next pf
Next iPosition
End Sub
Option Explicit
Sub Expand_Entire_RowField_Other_Sheet()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long
Set pt = Worksheets(“Pivot Table”).PivotTables(“PivotTable1”)
iFieldCount = pt.RowFields.Count – 1
For iPosition = 1 To iFieldCount
For Each pf In pt.RowFields
If pf.Position = iPosition Then
For Each pi In pf.PivotItems
ActiveSheet.PivotTables(1).PivotFields(“Customer”).ShowDetail = True
ActiveSheet.PivotTables(1).PivotFields(“Category”).ShowDetail = True
‘ If pi.ShowDetail = False Then
‘ pf.ShowDetail = True
Exit Sub
‘ End If
Next pi
End If
Next pf
Next iPosition
End Sub
Sub Collapse_Entire_RowField_Other_Sheet()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long
Set pt = Worksheets(“Pivot Table”).PivotTables(“PivotTable1”)
iFieldCount = pt.RowFields.Count – 1
For iPosition = iFieldCount To 1 Step -1
For Each pf In pt.RowFields
If pf.Position = iPosition Then
For Each pi In pf.PivotItems
ActiveSheet.PivotTables(1).PivotFields(“Category”).ShowDetail = False
ActiveSheet.PivotTables(1).PivotFields(“Customer”).ShowDetail = False
‘ If pi.ShowDetail = True Then
‘ pf.ShowDetail = False
Exit Sub
‘ End If
Next pi
End If
Next pf
Next iPosition
End Sub
Hey Chuck,
Thanks for sharing. I don’t have a machine with 2010 on it right now to test. We might also be able to add some error handling around the ShowDetails check to bypass those errors. I’m not exactly sure what is causing it though.
The one important thing to note for others using your macro is that you will need to change the field names to match whatever fields are used in the pivot table.
Thanks again!
Jon,
While investigating using the debugger I opened the watch in the Immediate Window and found that pi.ShowDetail had a type of Integer when it is supposed to be a Boolean. I could not figure out why the type is set as Integer? I wished I could integrate a snapshot of the debugger with the value within this message.
Regards,
Dear Jon
Thank you so much for your email regarding Excel …. and I still want to keep in contact with you..
Again, thank you Jon
Regards,
Abraham lee
Jon, I recently started subscribing to your tips because of a new gig I have. In. Nutshell I need get better at Vlookup, Pivoting table and analyzing data between various spreadsheets at a rapid pace, like NOW.
What are your suggestions, please?
Also your tips are always AWESOME
Hi Scully,
Congrats on the new gig! 🙂
Here are some references to help. I have a free 3-part video series on the lookup formulas including VLOOKUP, INDEX, MATCH and more. Here is the link to register. It’s free.
https://www.excelcampus.com/lookup-formulas-free-training/
I also have a free webinar on getting started with pivot tables. Here is the link to register.
https://www.excelcampus.com/pivot-secrets-webinar
I hope that helps get you started. Both of those resources are precursors to my full in-depth courses.
Thanks again for your support! Happy to have you here! 🙂
Only after changing the pivot table I’ll get the same error as Chuck.
pi.ShowDetails errors when pi.Name equals “<05/01/2015"
Are there any requirements?
Hey Frank,
It sounds like this might be an issues with Excel 2010. I’ll have to get a machine with 2010 on it to test. In the mean time, if anyone has a workaround/solution, please feel free to post.
I’m not sure if the error is only raised on that pivot item, or the ShowDetails property.
Thanks Jon, for this great email.
I have not yet tested out the macros, but I wiil do.
If any query arise I’ll leave a msg. Thx again.
Thanks Edil! 🙂
Hi Jon,
Great macros! Will they work for Power Pivot either?
Thanks.
This is ace, thanks!
Jon,
I finally made to work the way it is supposed to work. Here below is the Macro, I have used the on error handling and have integrated a Public variable to make the iPosition move by one.
[CODE]
Option Explicit
Public iP As Integer
Public fPath As Boolean
Sub Expand_Entire_RowField()
‘Expand the lowest position field in the Rows area
‘that is currently expanded (showing details)
If fPath = False Then
iP = 1
fPath = True
Else
iP = iP + 1
End If
Dim PT As PivotTable
Dim PF As PivotField
Dim PI As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long
On Error GoTo ErrHandling
‘Create reference to 1st pivot table on sheet
‘Can be changed to reference a specific sheet or pivot table.
Set PT = ActiveSheet.PivotTables(1)
‘Count fields in Rows area minus 1 (last field can’t be expanded)
iFieldCount = PT.RowFields.Count – 1
‘Loop by position of field
For iPosition = iP To iFieldCount
‘Loop fields in Rows area
For Each PF In PT.RowFields
‘If position matches first loop variable then
If PF.Position = iPosition Then
‘Loop each pivot item
For Each PI In PF.PivotItems
‘If pivot item is collapsed then
If PI.ShowDetail = False Then
‘Expand entire field
PF.ShowDetail = True
‘Exit the macro
Exit Sub
End If
Next PI
End If
Next PF
‘If the Exit Sub line is not hit then the
‘loop will continue to the next field position
Next iPosition
ErrHandling:
PI.ShowDetail = True
Resume Next
‘ Set pt = ActiveSheet.PivotTables(1)
‘
‘ iFieldCount = pt.RowFields.Count – 1
‘
‘ For iPosition = 1 To iFieldCount
‘ For Each pf In pt.RowFields
‘ If pf.Position = iPosition Then
‘ For Each pi In pf.PivotItems
‘ If pi.ShowDetail = False Then
‘ pf.ShowDetail = True
‘ Exit Sub
‘ End If
‘ Next pi
‘ End If
‘ Next pf
‘ Next iPosition
End Sub
[/CODE]
Thanks Chuck for this. do you have the same for collapsing the fields.
Thanks Jon, for this great email and great instruction!
Hi Jon,
Happy Friday to all the members of your blog! By the way have you had the chance to check in Excel 2010 why would the ShowDetail by a type of Integer when it is supposed to be Boolean?
Cheers!
Jon, a great macro, but it seems not to work for powerpivots.
Replacing ShowDetail with DrilledDown alone is not sufficient.
The problem is that VBA can’t read the DrilledDown status, if it is not an OLAP data source (whatever that means).
This clumsy Workaround worked for me:
If WorksheetFunction.CountA(Range(Cells(19, iPosition + 1), Cells(Rows.Count, iPosition + 1))) = 0 Then
But it it is neither elegant nor flexible as the starting position below the header needs to be defined for each pivottable individually.
Do you have a better solution? Could pt.pf.DataRange be somehow applied?
Have you ever figured this out? At this point a non-elegant solution would be good enough for me.
Where do I insert your line of code? Instead of the “if pivotfield is collapsed” part?
Chuck had asked about “Type mismatch” for date type fields.
No need to check in 2010 as it happens also in 2013 (and most likely also in 2016).
‘Loop each pivot item ‘error happens at first pi of any date pf
For Each pi In pf.PivotItems ‘pi =”1/31/1979″ instead of 31.01.1979
‘If pivot item is collapsed then
If pi.ShowDetail = False Then ‘pi.ShowDetail =
I do not know if this matters, but my date example from above is a text from a download tranfered into date format:
Range(“AN24”).Select
With Range(Selection, Selection.End(xlDown))
.NumberFormat = “dd.mm.yyyy”
.Value = .Value
End With
I love these macros! The only problem I am having is when I use them on a pivot chart in another workbook, your example file opens after the change is made in my pivot table.
Also, a suggestion to improve would be to have some sort of toggle feature for the Expand/Collapse Rows and Columns macros. Something like your Case Text macro. Then you could consolidate 4 separate macros into 2.
Solved my own issue, the command buttons are there for a reason!
Super excited to use your macros! Thank you for taking the time to share and post the code! 🙂 Only “improvement” I would suggest is to add a comment in the top with how you like to be memorialized. I like to give credit to my sources in my comments at top of macros (for my own reference if I need to return for more info / related question… and to give proper credit). True, I can post my own, but commenting to give yourself credit also is a gentle reminder that someone did the heavy lifting for me… 🙂 Again, THANK YOU for posting!
This is really useful. How would the macro’s be adapted to work when there are two pivot tables on one worksheet and you want to expand/collapse both pivot tables at the same time?
This is very interesting, but I notice yours has the same issue mine does – the ActiveCell doesn’t track to the expand/collapse. To wit, if I the cursor is on, say, B16, and after you expand it, the cell where you were is now like, B37. How do I get the cursor to move with the filtering? Thank you
please assist with a similar code that works in a data model pivot table
A version that works with data models would be very helpful!
Great job John! Well done! Thank you !
Nice macro!
One of my systems generates an Excel workbook with a few hundred worksheets, about 30–50 of which contain pivot tables. All the pivot tables are collapsed (by row), and they all need to be opened for display purposes, which is tedious.
Is there a way to extend your macro so that it can expand all rows of all pivot tables in all worksheets at once (i.e., loop through every worksheet and execute the macro on each one)?
Sheets that don’t contain pivot tables would need to be left unmodified.
Never mind, Wyn Hopkins came up with the solution by extending your code here:
https://techcommunity.microsoft.com/t5/excel/way-to-automatically-expand-all-pivot-tables-in-a-notebook/m-p/2069721/highlight/false#M86258
Hi Jon,
I have tried the following modification and it seems to work well:
Sub Expand_Entire_RowField()
‘Expand the lowest position field in the Rows area
‘that is currently expanded (showing details)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long
‘Create reference to 1st pivot table on sheet
‘Can be changed to reference a specific sheet or pivot table.
Set pt = ActiveSheet.PivotTables(1)
‘Count fields in Rows area minus 1 (last field can’t be expanded)
iFieldCount = pt.RowFields.Count – 1
‘Loop by position of field
For iPosition = 1 To iFieldCount
‘Loop fields in Rows area
For Each pf In pt.RowFields
pt.PivotFields (pf)
‘If position matches first loop variable then
If pf.Position = iPosition Then
pf.ShowDetail = True
End If
Next pf
‘If the Exit Sub line is not hit then the
‘loop will continue to the next field position
Next iPosition
End Sub
Macros are great for this – and I’ve used them myself – but they either have to be added to each file or to the PERSONAL.XLSB.
Me? I’m loving your graph button trick! 🙂 This will be a useful create-on-the-fly tool to add for end users who can’t remember all the other methods.
Thanks.
Hi, looks really useful …. what does this error mean please? (bottom line)…”If pi.ShowDetail = False Then”
Sub Expand_Entire_RowField()
‘Expand the lowest position field in the Rows area
‘that is currently expanded (showing details)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long
‘Create reference to 1st pivot table on sheet
‘Can be changed to reference a specific sheet or pivot table.
Set pt = ActiveSheet.PivotTables(1)
‘Count fields in Rows area minus 1 (last field can’t be expanded)
iFieldCount = pt.RowFields.Count – 1
‘Loop by position of field
For iPosition = 1 To iFieldCount
‘Loop fields in Rows area
For Each pf In pt.RowFields
‘If position matches first loop variable then
If pf.Position = iPosition Then
‘Loop each pivot item
For Each pi In pf.PivotItems
‘If pivot item is collapsed then
If pi.ShowDetail = False Then ; this line