How to hide empty rows in pivot table

[helsuze]

I have a pivot table that pulls in data from another sheet within the workbook. Depending on what report filter is selected in the report filter field within the pivot table, the resulting table varies in size, e.g. it can occupy cells A4:D10 or A4:D50 depending on the filter selected. Below the pivot table is further data, which is separate to the pivot table data but needs to be displayed on the same page.

The further data below the pivot starts at row 51, as this is the first row that is definitely free when the pivot table is its maximum size, ie occupying cells A4:D50. However, depending on the report filter in operation, the pivot table is not always that big, so if the pivot table occupies cells A4:D10 for example, rows 11-50 are completely empty. I would like to be able to automatically hide these rows if there is no data in them, ie if the pivot table filter returns a smaller data set, I want the unused rows below it to be hidden in that view. In the example above, this would mean rows 11-50 would be hidden.

So what I want would be something like this:
If cells A4:D50 = ""
Then Hide.EntireRow

If cells A4:D50 = "has any value"
Then Show.EntireRow

Can anyone advise on how to do this? Thanks!

 

[texasalynn]

take a look at this

 

[helsuze]

Hi there,

Thanks for this, however, when I use this code it actually hides all the rows in the specified range, regardless of whether they are empty or not. I think this is somehow related to the fact the code looks for the sum of the values in the rows, and although there are numbers in my pivot table, perhaps it treats numbers in a pivot table differently? Certainly, the code works when I create a simple test file with numbers in it (and no pivot table), but does not work in the case of my pivot table, even though there are numbers displayed within the range specified?

Any ideas? Thanks!