Hide Rows Based on Formula Result

[PHIL.Pearce84]

Hi

I dont know much about VBA but can anyone help?

I need a macro that will hide all rows where the formula result is zero as a result of the cost centre selected from a validation list?

 

[MrKowz]

Welcome to the forums!

Why not autofilter the data to show only non-zero values?

 

[VoG]

Try like this: it checks A1:A10 for zeroes. Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Calculate()
Dim i As Long
For i = 1 To 10
    Rows(i).Hidden = Range("A" & i).Value = 0
Next i
End Sub

 

[PHIL.Pearce84]

Hi

I changed the column to Column M and the rows from 16 to 102 and this works but it seems to keep running until I press esc? Also is there anyway for it to not hide row 94?

Thanks for such a quick response

 

[VoG]

Try

Private Sub Worksheet_Calculate()
Dim i As Long
Application.EnableEvents = False
For i = 16 To 102
    If i <> 94 Then
        Rows(i).Hidden = Range("M" & i).Value = 0
    End If
Next i
Application.EnableEvents = True
End Sub

 

[PHIL.Pearce84]

Perfect thank you very much

Just out of curiousity should I need to avoid hiding another row at a later date what would I have to do?

 

[VoG]

Try like this

Private Sub Worksheet_Calculate()
Dim i As Long
Application.EnableEvents = False
For i = 16 To 102
    If i <> 94 And i <> 96 Then
        Rows(i).Hidden = Range("M" & i).Value = 0
    End If
Next i
Application.EnableEvents = True
End Sub

 

[MrKowz]

To expand on this a bit to prevent excessive AND statements:

Private Sub Worksheet_Calculate()
Dim i           As Long, _
    ExcludeRows As Variant
    
Application.EnableEvents = False
ExcludeRows = Array(94, 96) 'add row numbers here to exclude
For i = 16 To 102
    If Not IsError(Application.Match(i, ExcludeRows, 0)) Then
        Rows(i).Hidden = Range("M" & i).Value = 0
    End If
Next i
Application.EnableEvents = True
End Sub

 

[PHIL.Pearce84]

Hi Guys the Macro above has suddenly stopped working and I can see why? It has been fine but just stopped I and tried to repaste it but to no avail Any ideas?

 

[MrKowz]

Can you please copy/paste the macro you are using here? Have you changed the spreadsheet in any way?