Macro-Hide Rows in All Sheets Based on Criteria in First Sheet

[belang46]

Hi everyone,

I hope this note finds all doing well. I'm stuck on a macro i'm trying to write for work. Basically, there are 3 sheets that feed into one "Consolidated Sheet". I am trying to have all three sheets show the same amount of rows, based on data in the consolidated sheet.

For example, if in the consolidated tab, rows 1-5 have data, but there is no data in 6-10...I want columns 1-5 in ALL sheets shown, and 6-10 in ALL sheets hidden.

I figured if I just run a simple code, and select all sheets at the beginning of the macro, it'd work on all sheets...this unfortunately is not the case. I'm sure it's a simpleton mistake, but any help would be GREATLY appreciated.

Could anyone please have a look and let me know if theres an obvious problem I can't seem to identify?

Thanks in advance!!

My code is below:

Option Explicit
Const strUnitsRange As String = "Roadmap_Hide"
Const strOrderSheet As String = "Global"
Const strUnitsRange2 As String = "Action_Plan_Hide"
Sub HideRows()
    Dim cel As Range
    
    ThisWorkbook.Sheets.Select
    
    For Each cel In Sheets(strOrderSheet).Range(strUnitsRange).Cells
        If cel.Value = 0 Then cel.EntireRow.Hidden = True
        If cel.Value <> 0 Then cel.EntireRow.Hidden = False
    Next cel
    
    For Each cel In Sheets(strOrderSheet).Range(strUnitsRange2).Cells
        If cel.Value = 0 Then cel.EntireRow.Hidden = True
        If cel.Value <> 0 Then cel.EntireRow.Hidden = False
    Next cel
    
    Sheet1.Select
    
End Sub

 

[belang46]

I'm also looking to see if there's a "cleaner" way to write the code...i'd assume theres some way to call both ranges in just 1 For Statement instead of if.

Please let me know if you have a moment-thanks!