Automatically refresh pivot table filter to remove spaces and blanks

[CaptainGravyBum]

Hello,
I have a pivot table with chart in my workbook and there are a lot of empty and (blank) cells in the data reference.
When the data changes I have to go to the filter and select all, before unselecting the empty and (blank) values otherwise it will not select any of the new fields in my pivot.
Is there a way to get excel to do this for me when the sheet is refreshed? I tried recording a macro but that didn't work, I also tried the following VBA, but that doesn't pick up any new fields in my data.

Application.EnableEvents = False
ThisWorkbook.RefreshAll
Application.EnableEvents = True

 

[Sergius]

If I understand correctly, then add this code to the sheet module. Replace the range with yours

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
ActiveSheet.Range("$A$10:$T$98").AutoFilter Field:=1, Criteria1:="<>"
Application.EnableEvents = True
End Sub

 

[Alex Blakenburg]

Is the filter a page filter or an item (row filter) ?
Do you really want to refersh all the pivots or just the one on the sheet ?

Assuming its a row field perhaps something like the below.

Application.EnableEvents = False
ThisWorkbook.RefreshAll                                 ' Just the 1 table something like Me.PivotTables(1).RefreshTable

With Me.PivotTables(1).PivotFields("Col1")              ' Put in your Field name and optionally your pivot table name
    .ClearAllFilters
    .PivotItems("(blank)").Visible = False
End With
Application.EnableEvents = True

 

[CaptainGravyBum]

Hi @Alex Blakenburg,
I have three pivot tables on one sheet, they don't have a great deal of data in them but I would like them all to refresh when the sheet is selected.
The problem I'm having is that the row filter doesn't pick up any new fields to select, I have applied a filter for specific rows to exclude blanks etc so it isn't automatically selecting all.
I'm assuming I need something to say [Select all on the filters then deselect Spaces and (Blanks)].

 

[Alex Blakenburg]

I need something to say [Select all on the filters then deselect Spaces and (Blanks)].

That's what the code I gave you is supposed to do, although only on the first pivot.
Did you try it ? If it works we can modify it to cycle through all the pivots on that sheet.

Is the row name the same on all 3 pivots and what is it?

 

[CaptainGravyBum]

If I understand correctly, then add this code to the sheet module. Replace the range with yours

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
ActiveSheet.Range("$A$10:$T$98").AutoFilter Field:=1, Criteria1:="<>"
Application.EnableEvents = True
End Sub

Hi @Sergius,
Sorry, I may be being stupid, but I don't understand what range to apply this to. I tried to apply it to the pivot table filter range, but it gives an error 1004. I also tried applying it to the whole data range and that is the same error. Is there a simpler way to apply a direct filter on the range which doesn't include spaces or blanks?

 

[RoryA]

Change the field settings for the field(s) you are filtering to include new items in manual filters. Then you won't need code.

 

[CaptainGravyBum]

Change the field settings for the field(s) you are filtering to include new items in manual filters. Then you won't need code.

The data keeps changing when the spreadsheet has more information adding, so it isn't picking up the new fields. I can just do it manually, but this is for the board members so I need to make it as automated as possible.

 

[RoryA]

I assume you mean new items in a field, in which case my suggestion will resolve that. If you actually mean new fields in the data (I.e. new columns in the data source) then I don’t really see how that relates to your original question.

 

[Sergius]

There is a line in the code

ActiveSheet.Range("$A$10:$T$98").AutoFilter Field:=1, Criteria1:="<>"

replace the range $A$10:$T$98 with yours