(Solved) – Macro that Hides Rows Doesn’t Update When Named Range Changes Sizes


I compiled some code that hides rows based on some certain criteria. At the beginning of the code, I unhide all the rows so that when the sheet recalculates the correct rows hide. Here is the code:

    Private Sub Worksheet_Calculate()

Dim ws As Worksheet
Dim primaryarray As Range
Dim rw As Range

Set ws = ThisWorkbook.Sheets("Experience Rating Sheet")
Set primaryarray = ws.Range("B9:M322")

Application.ScreenUpdating = False

' unhide all rows before we begin
primaryarray.EntireRow.Hidden = False

For Each rw In primaryarray.Rows
    rw.EntireRow.Hidden = BlankOrZero(rw.Cells(4)) And BlankOrZero(rw.Cells(8))
Next rw

Application.ScreenUpdating = True

End Sub

Function BlankOrZero(c As Range)
BlankOrZero = Len(c.Value) = 0 Or c.Value = 0
End Function

The range that it is checking is a named range that shows


in order to get rid of random zeros.

I have to manually press the the calculate worksheet each time to fix it because the calculation is on manual.

I have tried putting Worksheet.Calculate in the Module to compensate for this but it has not worked.

By compensate, I mean automate this calculation in order to change how many rows are hidden.

Note: The source for the named range is also 2 different sections that have dynamic formulas that change sizes based on criteria I set. Using index/match for one and filter/sortby formulas for the other.

Leave a Reply

Your email address will not be published. Required fields are marked *