A few words of thanks would be greatly appreciated.

Excel - Conditional hide rows in multiple sheets


I have one excel file with multiple sheets, and all of them with the same columns and rows . I need to hide specific rows in every sheet (the same row numbers for all the sheets) if the value is zero in a specific cell.
So, how can I do it in VB and automatically updated upon changing the value in previous specified cell.

i.e: I have 10 sheets for ten employee and in every sheet I have the following data:

A1= Employee Name 
A2= Age, 
A4= Salary, B4= 4000 (all the amounts (values) will differ in each sheets) 
A5= Deductions, B5=500 
A6= Insurance, B6=1000 
A8= Net Salary, B8=2500 

A10= Paid by Check 
A11= Paid by Transfer, B11= 2500 
A13= Total, B13= 2500 

My requirement is to hide each row that has zero in column B in all the 10 sheets. Of course the value for all the sheets are taken from one master sheet, so if the value in the master sheets changed it will be changed also in the 10 sheets upon each correspondent.

Note: I don't need to filter the Values.

In brief I want to write a macro to hide all the rows (specified) that have a zero in the B column, and to be applied on the 10 sheets that have the same rows and columns.


  • Open the VBE
  • Click on your master sheet
  • Paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bHide As Boolean

    If Target.Column <> 2 Then Exit Sub
    bHide = True
    If (CStr(Target) <> "0") Then bHide = False
    For Each Sheet In Sheets
        If Sheet.Name = ActiveSheet.Name Then GoTo Next_Sheet
        Sheets(Sheet.Name).Rows(Target.Row).Hidden = bHide

End Sub


Thanks to rizvisa1 for this tip on the forum.

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.


This document, titled « Excel - Conditional hide rows in multiple sheets », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).