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.