FILTERS inside Pivot Table does not update when source data (incl VBA) changes

Johnhtvu

New Member
Joined
Feb 25, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. MacOS
Hi,

question about pivot table FILTERS (see picture)

Situation:
I erased my source data and my pivotable refreshes correctly as seen in the picture. this happens automatically as I am using a little VBA code in the source data to refresh my pivot tables automatically.

However, when you click on filter, you can still see the source data - while I already erased the source data

If I add new data to my source data - the item is not selected in the filters and therefore, does not appear in the pivot table.
Does anyone know what happens here?

Thanks.


Screenshot 2021-03-10 at 22.38.26.png
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Maybe you can use VBA to refresh the cache.
When your last item was deselected. All new items are als deselected. Also you can use VBA to select all the items.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Look also to Missing Items.
 

Johnhtvu

New Member
Joined
Feb 25, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. MacOS
Update:
I figured that you need to ''select all' in the filter so that new information in the source data will be selected.
However, I had 'blank' deselected as I don't want that data in my pivotable.

However, I prefer to have it without the blanks.

see these 2 pictures.

Still not been able to solve why my pivotable refreshes but the information stays in the filters
 

Attachments

  • Screenshot 2021-03-10 at 23.06.01.png
    Screenshot 2021-03-10 at 23.06.01.png
    229.9 KB · Views: 3
  • Screenshot 2021-03-10 at 23.06.01.png
    Screenshot 2021-03-10 at 23.06.01.png
    229.9 KB · Views: 3

Johnhtvu

New Member
Joined
Feb 25, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. MacOS

ADVERTISEMENT

Maybe you can use VBA to refresh the cache.
When your last item was deselected. All new items are als deselected. Also you can use VBA to select all the items.
Hi, I am not sure what you mean.

I am using this VBA code on my source data:

----------
Private Sub worksheet_change(ByVal target As Range)

ThisWorkbook.RefreshAll

End Sub
-------------

Pivottables and all work fine. However, I just don't understand why the values are still in my filters, while source data is empty.
 

Johnhtvu

New Member
Joined
Feb 25, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. MacOS
I resolved the issues with the filters by doing this:

Pivot table options -> Data -> Deselect "retain items deleted from data source''

---

Problem remains: if source data is empty: the pivot table by default 'selects all' - including blanks.

Any way to exclude blanks by default?
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Mabe you can use this:

VBA Code:
    ActiveSheet.PivotTables("myPivottable").PivotCache.Refresh
    ActiveSheet.PivotTables("myPivottable").PivotFields("myField").ClearAllFilters
    ActiveSheet.PivotTables("myPivottable").PivotCache.MissingItemsLimit = xlMissingItemsNone
    For Each pField In ActiveSheet.PivotTables("myPivottable").PivotFields
        On Error Resume Next
        pField.PivotItems("(blank)").Visible = False
    Next
 

Johnhtvu

New Member
Joined
Feb 25, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. MacOS
Mabe you can use this:

VBA Code:
    ActiveSheet.PivotTables("myPivottable").PivotCache.Refresh
    ActiveSheet.PivotTables("myPivottable").PivotFields("myField").ClearAllFilters
    ActiveSheet.PivotTables("myPivottable").PivotCache.MissingItemsLimit = xlMissingItemsNone
    For Each pField In ActiveSheet.PivotTables("myPivottable").PivotFields
        On Error Resume Next
        pField.PivotItems("(blank)").Visible = False
    Next
I managed to go without vba by:

1) Pivot table options -> Lay out -> Allow multiple filters
2) Deselect retain item deleted from data source
 

Watch MrExcel Video

Forum statistics

Threads
1,130,327
Messages
5,641,541
Members
417,218
Latest member
dchapman17

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
Top