Excel pivot table query - retaining filters on pivot in absence of data

[Markylex]

Hello all,

I have a pivot table / potential Excel VBA related query, which I am hoping someone might be able to assist with?

Within the Excel VBA project I am currently working on, I have a pivot table that is filtered to look at invoices in my raw data, that have a 'withheld' or 'allocated' status. As this raw data comes from different suppliers, there may be times where it doesn't contain either 'withheld' or 'allocated' invoices, however I still want the pivot table to retain the same filters and display nothing in such instances. Whilst I have recently become familiar with the pivot table option 'show items with no data', which would ordinarily solve this issue, this currently does not work for me. This is due to a part in the macro I have created, which updates the source data of all the pivots in my workbook, based on the new set of raw data provided.

At present, I can think of two ways to fix my issue:

• Adjust the source data on my pivots to be 'static' and look at select columns in the raw data, rather than updating them via the macro to match the exact number of rows in the raw data. I am unsure if this goes against 'best practice' though, as the pivot will be picking up a lot of empty cells, which will obviously filter through to the pivot table as 'blank'.
• Add ficticious data into the raw data, to ensure there's always a row that has the 'withheld' and 'allocated' status, however assign nil values to these. Once the macro runs, including updating the source data for the pivots, I could then have these further steps as part of the macro:
  • Apply the 'show items with no data' option to the relevant pivot.
  • Delete out the fake data from the raw data.
  • Refresh the pivot.
  • This way, the only issue would be the small number of blank lines in the absence of the fake data, which I guess could also be filtered out of the pivot? I assume this beats the pivot searching through whole columns though?

I wondered what people's opinions were on the above two points, or if anyone had any alternative suggestions to remedy my situation? For a bit of added context, this project is for the reconciliation of supplier statements and is for a different team in my company to my own. As such, I am trying to avoid any presentation issues with any of the pivots and also preventing the need for people using the file to manually mess around with the pivots/data.

I hope this makes sense, however should anyone have any questions/require further information, then let me know and I am happy to provide.

Thanks in advance for any advice provided!

 

[Alex Blakenburg]

What are your thoughts on this - is there anything you'd recommend I change?

If you decide not to start with no rows in the table then the lastrow code will only find the last row in the table.
This is fine if you don't have blank rows in the table but if you do you will need to switch to using "Find".

is there any chance you could explain the purpose of this line in a little more detail, including the chosen range:

newrow.Range.Resize(, 3).Value = Range("I10:K10").Value
This is an alternative to Copy then PasteSpecial Values, generally thought to be faster.
For copy paste you just need to specify 1 cell for the destination which will be the top left corner of where you copy the data.
To set the destination = source data (assign the source data to the destination range), the size of the range needs to match exactly, so if on the right hand side we have Range("I10:K10") being 1 row by 3 columns we need to Size (resize) the left side to be 1 row and 3 columns.