Hi everyone,

So I spent a couple of days searching for an answer, but I've had no luck so far. I am working on a dashboard and have a few pivot tables one right on top of the other. I have enough rows hidden between them so that I don't get the pivot tables cannot overlap another error, but the code I'm using seems inefficient. I want the rows to unhide when they become populated with data, ie after I expand a field, and hide the rows when they are empty.

Here is the code I am currently using. 

Private Sub Worksheet_Calculate()
Dim cell As Range
For Each cell In Range("b13:b97")
    If cell.Value = "" Then
        cell.EntireRow.Hidden = True
    ElseIf cell.Value <> "" Then
        cell.EntireRow.Hidden = False
    End If
Next cell

End Sub
The problem is that every time I expand or collapse a field the for loop checks through all of the cells and can take a couple seconds, which does not seem user friendly for the people who read the report. I am trying to figure out how to exit out of the for loop when it unhides the necessary amount of rows, ie. if I expand a field and it populates 10 cells with data, then I want the macro to unhide the 10 cells and then stop. When I collapse a field I want the macro to hide the cells that no longer have any data in them and then stop the for loop there without looping through all 84 rows.

I hope this is enough information. Please let me know if other details are required.

Thank you!