How to hide empty rows in excel

[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 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.

Someone suggested previously that I use this method but the problem here is that 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.

Can anyone advise on how to do this? Thanks!

 

[Ralajer]

Try this it should work it is fairly general except the data has to be in Column A which you mentioned it is.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Dim endRowPT As Long, startRowData As Long
 
    '// Unhide all rows
    Cells.Rows.EntireRow.Hidden = False
    '// Error Handling to prevent error on rearranging some table fields
    On Error GoTo ERR:
    '// Row number of end of pivot table
    endRowPT = Target.RowRange.Row + (Target.RowRange.Count - 1)
    
    '// Row Number of start of data
    startRowData = Range("A" & endRowPT).End(xlDown).Row

    '// Hide all rows from end of Pivot Table + 1 to Start of data
    Rows(endRowPT + 2 & ":" & startRowData - 1).EntireRow.Hidden = True

ERR:
End Sub

 

[helsuze]

That is great - works perfectly. Thanks so much!