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

 

[CaptainGravyBum]

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

I think this is where i'm going wrong, as with the data fields, my range expands when new data is entered so I'm putting the data range as the whole column.

 

[RoryA]

I'm putting the data range as the whole column

You should never do that with a pivot table. Either use a Table or a dynamic named range.

 

[Alex Blakenburg]

Did you try @RoryA suggestion in Post #7 ?

• Right click on the field that you are filtering out blanks
• Select Field Settings
• Using the tab Subtotals & Filters at the very bottom check the box next to "Include new items in manual filters"

 

[CaptainGravyBum]

Did you try @RoryA suggestion in Post #7 ?

• Right click on the field that you are filtering out blanks
• Select Field Settings
• Using the tab Subtotals & Filters at the very bottom check the box next to "Include new items in manual filters"

To be honest, I didn't really understand how to do this until you sent me the screenshot, so thanks.
I have just tried and unfortunately, when I tick the box it crashes out of the worksheet, no idea why.

 

[CaptainGravyBum]

I think I've cracked it now, with thanks to everyone's help. I managed to apply the 'include new items' part and alongside the refresh all on the sheet selection it is refreshing all data and including any new items.
Thanks again.

 

[Alex Blakenburg]

Glad you got it working.
I was wondering how we were going to trouble shoot your comment of "it crashes out of the worksheet" without access to it