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

(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

=IF(Expected_Actual_Losses="","",Expected_Actual_Losses)

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 *