VBA sort pivot tables and hide blanks

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
435
Office Version
  1. 365
  2. 2010
Hello,

I'm trying to sort the pivot tables on my sheet and hide the blank entries in the row field

My code so far seems to sort but fail on the pf.PivotItems("").Visible = False part.
VBA Code:
Sub SortPivots_HideBlanks()
    Dim pt As PivotTable
    Dim pf As PivotField
    For Each pt In ActiveSheet.PivotTables
        For Each pf In pt.RowFields
            pf.PivotItems.AutoSort xlDescending
            pf.PivotItems("").Visible = False
        Next pf
    Next pt
End Sub

What am I doing wrong?
 
The truth is, I am not very experienced at the Pivot Table side on the worksheet, so it is hard to debug without having the particular data set and PT structure that fails. I tried to create a data set by looking at your sample data, however, it didn't help so much as I can see.

Although I am not sure the following will help either, we can use the first pivot field with the index number instead of providing the field name. I am trying to avoid looping through row fields as I don't think it is necessary and also the reason for the failure.

VBA Code:
Sub SortPivots_HideBlanks()
    Dim pt As PivotTable
    For Each pt In ActiveSheet.PivotTables
        With pt.PivotFields(1)
            .AutoSort xlDescending, .Name
            .PivotItems("(blank)").Visible = False
        End With
    Next pt
End Sub
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
...we can use the first pivot field with the index number instead of providing the field name. I am trying to avoid looping through row fields as I don't think it is necessary and also the reason for the failure.
Hello Again,

I didn't know that pivotfields could be referenced by number. Trying this, I found that PivotFields(1) is the first in the list of available fields, rather than visible fields, so it ran without errors but didn't change the visibility of the blanks because pivotfield number 1 wasn't ever one of those selected for any of the tables.

I managed to get the following to work to hide the blanks, but it's inefficient/ runs slowly with 100 or so tables and stops with an error when it can't find anymore blank rowfields.

VBA Code:
Sub SortPivots_HideBlanks5()
        Dim pt As PivotTable
        Dim pf As PivotField
        Dim pi As PivotItem
        
    For Each pt In ActiveSheet.PivotTables
 
        For i = 1 To pt.RowFields.Count
          For Each pi In pt.RowFields(i).PivotItems
              If pi.Name = "(blank)" Then
                    pi.Visible = False
                    Exit For
                End If
            Next pi
        Next i
 '       pf.AutoSort xlDescending, .Name <--This is what I tried for sorting, but it fails to compile "invalid or unqualified reference"
    Next pt
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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