This pivot table looks awkward. The countries without provinces have a lone detail line, followed by a subtotal line with the exact same information. You can fix this by collapsing the single-item rows one at a time, but that’s time-consuming, and boring. Wouldn’t it be much more fun to write some code to hide pivot table single-item subtotals?
A year or so ago I did just that. In reviewing it for this post I cleaned it up and learned a few things about where it works and where it doesn’t. When I run it the result looks like this, with the duplicated population counts nicely hidden:
The heart of the code is this simple routine. You pass it a single PivotField and it hides the details for any item in that field that contains just one row:
Dim ptItem As Excel.PivotItem
For Each ptItem In pvtField.PivotItems
If ptItem.RecordCount > 1 Then
ptItem.ShowDetail = True
Else
ptItem.ShowDetail = False
End If
Next ptItem
End Sub
In my first version of this code I used ptItem.DataRange.Rows.Count. This caused errors with hidden items, because their DataRange is Nothing. With ptItem.RecordCount it just sails on through.
The main procedure checks whether the cursor is in a pivot table and a few things like that. It then calls a function that returns only the visible pivot table row and column fields:
Dim PivotFieldNames() As String
Dim pvtField As Excel.PivotField
Dim i As Long
Dim PivotFieldsCount As Long
PivotFieldsCount = 0
With pvtTable
ReDim Preserve PivotFieldNames(1 To .PivotFields.Count)
For i = LBound(PivotFieldNames) To UBound(PivotFieldNames)
Set pvtField = .PivotFields(i)
If pvtField.Orientation = xlColumnField Or _
pvtField.Orientation = xlRowField Then
PivotFieldsCount = PivotFieldsCount + 1
PivotFieldNames(PivotFieldsCount) = pvtField.Name
End If
Next i
End With
ReDim Preserve PivotFieldNames(1 To PivotFieldsCount)
GetPivotFieldNames = PivotFieldNames
End Function
The returned pivot fields are passed to a userform not unlike this one, except that it allows you to pick multiple items. That way you can collapse more than one field at a time. (I don’t show the code here, but you can get it all from the download link at the end.)
The form looks like this:
Collapsing single-item rows works great for lists like this one of continents, countries and provinces, because if a country has no subdivisions there’s no further detail to show. It’s just Iceland.
Where it doesn’t make as much sense is with something like sales by year, month, week and date:
Even if you had sales in only one week in February, you’ll probably call it “week 6”, or “February 12 to 19”, or something. But when you collapse February that detail gets hidden.
Even more limiting is that you can’t collapse February for one year and leave it expanded for another. At least, I can’t find any way, either in VBA or in Excel. You either show a total for all your Februaries, or for none. In the example above it would be nice to hide the February 2012 total, but show it for 2013. If anybody knows a way to do that, please let us know.
To fool around with this for yourself you can download a workbook with a couple of sample pivots and the Single-Item Subtotal hider.
NOTE: I noticed a ways into this post that the population data is old. I tried to find something more recent, but didn’t come up with anything that had the provinces/states data. But it was nicely packaged in an Access database.
Doug, I hope you’re well. This is a surprisingly old post without comments. Your post, though, could be incredibly useful. In the last couple years I’ve dabbled in VBA and most recently started thinking about ways I can use it easily by copying/pasting simple subs to powerfully change a workbook.
I did just that with your attached workbook and script. I imported the three modules and tried to run it on my pivot table. Then, I checked if your assigned macro button worked. Neither do. I received an error that Dim ufChooser As frmChooserMulti was not an appropriate declaration.
I suppose the issue is that Excel has changed over the last eight years. Do you have any insight why I can’t run this successfully in Excel 365? And do I need all three modules to run? There are a lot of utilities that I am not advanced enough to think I know what they are. Unless you tell me otherwise, it seems like some may be for your own benefit and may not be necessary for running this particular one.
Any help would be awesome. Thanks so much.
Hey James,
I actually used this just the other day in office 365. I’m not looking at the code or the sample workbook, but the issue you’re dealing with currently is that you need frmChooserMulti in your project as well.
Drag that object into your project and see what happens. While you’re at it you will probably need to drag other things over too! Let me know if that makes sense and if it works.
Thanks Doug. That helps me get started. I imported three modules and the user form. I don’t see any other objects in the workbook.
The multiple choice list comes up and I check off the fields. But, nothing happens after that. Not even an error.It’s baffling because you’ve done an excellent job of making it dynamic, so I don’t think there is anything I need to change (table names, etc) in the code for it to work in mine.
I’m sorry to hear that, James. That’s a fairly wonky bit of code for fairly specialized pivot table configurationd, so I’m not sure what to suggest. Are you sure you’re picking the right field to collapse?