Hide and unhide rows based if total column is zero

[mike_ate_a_pie]

Hi all,

I'm new to VBA and trying to hide all rows in a profit and loss if the total value is zero.

Is it possible to apply this to several spreadsheets at once in the same workbook.

The total column is AS, and the table is C6:AS55

thanks

 

[Jon von der Heyden]

One way that may work for you is to use the Workbook_SheetActivate event.

Paste this into the ThisWorkbook module:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim arrSheets()
    Dim lngRowNum As Long
    
    Application.ScreenUpdating = False
    
    arrSheets = Array("Sheet1", "Sheet2", "Sheet3")
    If Not IsError(Application.Match(Sh.Name, arrSheets, 0)) Then
        For lngRowNum = 6 To 55
            Rows(lngRowNum).Hidden = Cells(lngRowNum, 45).Value = 0
        Next lngRowNum
    End If
    
    Application.ScreenUpdating = True
    
End Sub

Change arrSheets to the names of the desired sheets....

 

[mike_ate_a_pie]

cheers this was just what i'm looking for. It works great but does slow my spreadsheet down every time you click on a tab with the macro in. Is there anyway to stop this too?

Thanks