conditional hide rows in multiple sheets Solved/Closed

Question

It didn't solved yet.

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 automaticlly 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 requirment 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 correspondant.  

Note: I don't need to filter the Values.  

In breif 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

rizvisa1 · Answer

Open the VBE click on you master sheet and 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 Next_Sheet: Next End Sub

Conditional hide rows in multiple sheets

1 response

Similar discussions

Subscribe To Our Newsletter!