Select all PivotItems and uncheck (blank)

Centurion79b

New Member
Joined
Sep 21, 2015
Messages
11
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.


Code:
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")
RVary = Array("CMRVCS", "NMRVCS", "CQRVCS", "NQRVCS")
Application.ScreenUpdating = False
On Error Resume Next
For Each i In ary
Sheets(i).Activate
    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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Forum statistics

Threads
1,223,730
Messages
6,174,162
Members
452,548
Latest member
Enice Anaelle

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