Pivot table vba to filter out blanks

picklefactory

Well-known Member
Joined
Jan 28, 2005
Messages
506
Office Version
  1. 365
Platform
  1. Windows
Good morning folks
I'm hoping for some advice please, on how to get my pivot table to filter out blanks when refreshed.
I have an ever expanding range of values being evaluated in a simple pivot table that resides on the same sheet as the data range. The values are also pretty simple, just week no, a reject code and a quantity. I found some code to update the PT each time data is added to the range 'On worksheet selection change', but what I'd like to do is not have blanks displayed. I tried just deselecting them in the PT filter, but for some reason then any new reject codes I add, that have not been previously entered, also get deselected and do not display in the PT. If I do not filter at all then everything works OK except for the fact that I have a row and column for 'blank'. I guess that is due to the PT updating prior to the new data row being completed, so it is seeing blank data momentarily. Might there be a bit of simple code I could include with my refresh code, to switch off the filter whilst PT is updated and then re-filter after update? Or will that just loop?
Sorry, I'm a newbie on vba and pivot tables for that matter.
Hope that makes some sense.
Thanks folks

Code I'm using to refresh the PT is:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
   ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True

End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Woohoo!
Sorted it, sorry for false alarm folks. I've used the following which may be a bit clunky, but works.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With ActiveSheet.PivotTables("PivotTable5").PivotFields("Wk No")
.PivotItems("(blank)").Visible = True
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Code")
.PivotItems("(blank)").Visible = True
End With

Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
   ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True

With ActiveSheet.PivotTables("PivotTable5").PivotFields("Wk No")
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Code")
.PivotItems("(blank)").Visible = False
End With

End Sub
 
Upvote 0
Thank you! I realize it was a long time since you posted it but it works perfectly and I will be using it all of the time.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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