Rerun Excel macro when data gets updated

Question

I am trying to create a macro that hides rows if a cell in a specific column contains a 0 value.

I have found a macro from another forum to use that will hide the rows (below) but it will only hide rows the first time the data gets refreshed. I am using data validation cells at the top of the sheet that pulls in data from another sheet to populate reference data (and the rest of the sheet uses SUMIFS).

When my team updates the cell to select various options (Ex: USA, Asia, Europe, All), I need the macro to run again to hide any cells that contain a 0 value. I also can't have a macro that will mess with the SUMIFS formulas being used in the sheet.

Current formula I have:

Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlManual

For Each c In Range("E7:E153")
    If c.Value = 0 Then Rows(c.Row).Hidden = True
Next

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

I think all I need is to add some sort of rerun or loop lines at the end but am not sure where to go from here. Any help would be greatly appreciated.

TYVM.

"...hide any cells that contains a 0 value". Do you want to hide that row? Column? Or literally just that cell? You can't exactly (AFAIK) hide a single cell...but perhaps we can "hide" it by making the text white, or something? — BruceWayne

K Paul K Paul · Accepted Answer · 2017-08-10T15:39:46

You could use the Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then
        Application.ScreenUpdating = False
        Application.Calculation = xlManual

        For Each c In Range("E7:E153")
            If c.Value = 0 Then Rows(c.Row).Hidden = True Else Rows(c.Row).Hidden = False
        Next

        Application.Calculation = xlAutomatic
        Application.ScreenUpdating = True
    End If
End Sub

Rerun Excel macro when data gets updated

2 Answers