Autofilter a sheet that includes a Pivot Table

Hi All,

I'm a bit struggling with the following situation. I have a sheet that includes a pivot table. Given that the pivot table can dynamically change in size depending on the number of inputs it contains, my sheet includes 1000 empty lines (the pivot table can contain as much as 1000 elements) so it leaves enough room for the pivot table to expand. Obivously, I don't want to let 1,000 lines displayed if the pivot table is smaller than that. To solve this issue, I created a range that includes the pivot table + one additional column which is used as the filter. This column can be either 1 or 0 depending on the size of the pivot table. By auto-filtering this column, it automatically hides the lines that are not used by the pivot table, resizing my sheet to an appropriate dimension.

Attached is an example of what I'm trying to achieve (not that 0 and 1 are plugged for simplicity). I would assume that by using the following VBA, it would automatically filter the range called "table" using column 3 (which is the list of 1 and 0) and hide lines:

Please Login or Register  to view this content.

However, this is not the case and I get an error message telling me: "Run-time error "1004": Autofilter method of Range class failed".

I've used this technique with success in other sheets of my workbook but it does not seem to work in this sheet in particular. I dont know if this is related to the pivot table and I'm not sure to know how to resolve that.

Note that I get an error message in my example file. In my actual file, I dont get an error but nothing happens either so I guess something's wrong anyway.

Thanks for the help

For anyone interested, I wasn't able to have the aforementioned piece of code work with a pivot table in the middle. But an alternative way that seems to work is to manually filter the range (which works even if there's a pivot table inside) and then using the following code to refresh:

Please Login or Register  to view this content.

The refresh does not seem to have an issue with the pivot table

Thanks