Conditional hide rows in multiple sheets [Solved/Closed]

Question

Conditional hide rows in multiple sheets [Solved/Closed]

Report

issa - Apr 25, 2010 at 01:32 PM
rizvisa1

Posts: 4476
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: August 2, 2020

- Apr 25, 2010 at 08:22 PM

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

Conditional hide rows in multiple sheets
Excel conditionally hide rows based cell value - Forum - Excel
Excel delete same row in multiple sheets - Forum - Excel
Copy rows from multiple sheets to one sheet - Forum - Excel
Excel - Hide rows with zero value in Multiple sheets - How-To - Excel
Hide rows with zero value in Multiple sheets ✓ - Forum - Excel

1 reply

Ask a question

rizvisa1 · Accepted Answer · 2010-04-25T20:22:57-0400

Answer 1 / 1

rizvisa1

Posts: 4476
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: August 2, 2020

763
Apr 25, 2010 at 08:22 PM

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 [Solved/Closed]

1 reply

Your reply

Subscribe To Our Newsletter!