Running macro in each sheet which meet the condition

[manekankit]

I am having a macro to hide/unhide row based on cell value. I.e. if value in column A in respective cell is zero, then the row should be hidden and if the value in the column A in respective cell is not equal to zero, then the row should be visible. (E.g. if A5=0, row 5 should be hidden (if not hidden already), if A6<>0, it should be visible (if not visible already).

I am using below code which is working fine on each sheet.
Macro 1

Sub RowHide()
Dim A As Range
Set MX = ActiveSheet.Range("A2:A500")
For Each A In MX
If A.Value = "0" And A.Rows.Hidden = False Then
A.EntireRow.Hidden = True
End If
If A.Value <> "0" And A.Rows.Hidden = True Then
A.EntireRow.Hidden = False
End If
Next A
End Sub

Now I want to run this macro on all the sheets together at once. For that I am using below code, but the code is not running on all the sheets but only on one active sheet.
Macro 2

Sub RowHideAllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Dim A As Range
Set MX = ActiveSheet.Range("A2:A500")
For Each A In MX
If A.Value = "0" And A.Rows.Hidden = False Then
A.EntireRow.Hidden = True
End If
If A.Value <> "0" And A.Rows.Hidden = True Then
A.EntireRow.Hidden = False
End If
Next A
Next ws
End Sub

Need help to identify why the Macro 2 is not running on all the sheets. I am using office 365 64bit.
Also in Macro 2 I want to add one more condition. Macro should run only on those sheets where value in cell A1 of respective sheet is equal to “RowHide”.

 

[Fluff]

How about

Sub manekankit()
   Dim Ws As Worksheet
   Dim Cl As Range
   
   For Each Ws In ActiveWorkbook.Worksheets
      If Ws.Range("A1").Value = "RowHide" Then
         For Each Cl In Ws.Range("A2:A500")
            Cl.EntireRow.Hidden = Cl.Value = "0"
         Next Cl
      End If
   Next Ws
End Sub

In future please do NOT use bold test, we are perfectly capable of reading & it is considered rude.

 

[manekankit]

How about

Sub manekankit()
   Dim Ws As Worksheet
   Dim Cl As Range
  
   For Each Ws In ActiveWorkbook.Worksheets
      If Ws.Range("A1").Value = "RowHide" Then
         For Each Cl In Ws.Range("A2:A500")
            Cl.EntireRow.Hidden = Cl.Value = "0"
         Next Cl
      End If
   Next Ws
End Sub

In future please do NOT use bold test, we are perfectly capable of reading & it is considered rude.

Hi Fluff,

Apologies if my writing style sounded rude and thnks for revert.

I tried the code you suggested but it is not working. i think all the conditions in my original code are not captured.

i tried to modify your code as below but this is also not giving desired output.

Sub manekankit()
   Dim Ws As Worksheet
   Dim Cl As Range
      For Each Ws In ActiveWorkbook.Worksheets
      If Ws.Range("A1").Value = "RowHide" Then
         For Each Cl In Ws.Range("A2:A500")
            If Cl.Value = "0" And Cl.Rows.Hidden = False Then
            Cl.EntireRow.Hidden = True
            End If
            If Cl.Value <> "0" And Cl.Rows.Hidden = True Then
            Cl.EntireRow.Hidden = False
            End If
         Next Cl
      End If
   Next Ws
End Sub

 

[Fluff]

I tried the code you suggested but it is not working.

In what way isn't it working?

 

[manekankit]

In what way isn't it working?

Hi Fluff,
When I run the macro, nothing appears to be changing.

Rows with 0 in column A are not getting hidden and rows with non zero number in column A are not getting unhidden.

 

[Fluff]

Do those sheets have RowHide in A1? Remember that VBA is case sensitive so RowHide is not the same as Rowhide

 

[manekankit]

Hi Fluff,
When I run the macro, nothing appears to be changing.

Rows with 0 in column A are not getting hidden and rows with non zero number in column A are not getting unhidden.

Hi Fluff,

You spoted it right. I did not know that VBA is case sensitive. Now I am using the macro you suggested (post#2 and its modified version mentioned in post#3). Macro is running perfectly.

Thanks a lot for help.

Can you help me with one more thing? can you help me identify what was wrong in Macro2 that I mentioned in the question (post#1)?

 

[Fluff]

You set the range based on the Active, which doesn't change. If you replace ActiveSheet with Ws then it should work.