Select all PivotItems and uncheck (blank)


New Member
Sep 21, 2015
I have several pivot tables that only contain text comments by product and location. The problem I have is that when I refresh the pivot table the pivot items filter gets set to (blank) only which results in nothing being shown in the table. I tried writing code that would (select all) and then uncheck (blank) but there doesn't seem to be a way to (select all). So I have written some code to deselect (blanks) and select everything else. However this code runs really slow. It take more than 5 minutes to run through all the pivot tables. Here is my code:

There are 2 arrays in this code. Ary is an array of sheets that I want to process and RVary is an array of the pivot fields. Each sheet in Ary contain a pivot table called RVCS.

Any suggestions are greatly appreciated.

Dim ary As Variant, RVary As Variant
Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer
ary = Array("CM PSL", "NM PSL", "CQ PSL", "NQ PSL", "CM Country", "NM Country", "CQ Country", "NQ Country", "QTR Summary", "TY PSL", "TY Country")
Application.ScreenUpdating = False
On Error Resume Next
For Each i In ary
    For Each j In RVary
        With ActiveSheet.PivotTables("RVCS")
            With .PivotFields(j)
                For k = 2 To .PivotItems.Count
                    .PivotItems(k).Visible = True
                Next k
                .PivotItems(1).Visible = False
            End With
        End With
    Next j
Next i

Erase array1
Erase RVCS

Application.ScreenUpdating = True
Sheets("CM PSL").Select
SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...