picklefactory
Well-known Member
- Joined
- Jan 28, 2005
- Messages
- 506
- Office Version
- 365
- Platform
- 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:
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