I have a some pivot tables in a sheet, with lots of blank rows to separate them so that if one suddenly gets a lot of values it won’t overwrite the pivot table underneath. But to avoid the user having to scroll down quite far to see the other pivot table, I have a formula in column A which checks if anything is populated on each row and have then written some VBA to hide the rows that the formula has identified is empty.

The problem I’m facing is that it is taking 16 seconds just to hide the rows, and it’s not even a big range. Is there a better way I could do this?

For Each cell In Range("A1:A200")
    If cell.Value = "HIDDEN ROW" Then cell.EntireRow.Hidden = True
Next cell

The VBA runs in the Worksheet_Change and I ran debug to identify this time lag, and it’s results show:
| Timestamp | Step |
| ——– | ————– |
| 05/06/2024 13:22:27 | Start |
| 05/06/2024 13:22:27 | Pivot Value check completed |
| 05/06/2024 13:22:27 | Checks if the range changed is the Pivot Table filters |
| 05/06/2024 13:22:27 | Unhides all cells |
| 05/06/2024 13:22:27 | Clears filters |
| 05/06/2024 13:22:27 | Sets filters |
| 05/06/2024 13:22:33 | Reformatting colours in pivots |
| 05/06/2024 13:22:33 | Hiding Rows|
| 05/06/2024 13:22:49 | Finish |