::hide rows if value is 0::

[jekstein]

currently i have my code checking a specific cell and hiding if that cell = 0

example:

If Sheets("Total Sheet").Range("a7:a7").Select = 0 Then
    Rows("7").EntireRow.Hidden = True
    End If

I want to have my code check a range of cells and then if any of them = 0 i want it to hide the row its associated with.

Can anyone help me/??

THank you in advance

 

[jekstein]

when i click to run it, it pulls up a window looking for a macro to run

 

[Datsmart]

If you placed your code into a worksheet module like I mentioned earlier, you no longer run the macro by clicking anything, it runs after any change of that worksheet.
This is what the code should look like in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim HideRange As Range, c As Range

' Turn off Events to avoid continuous loop
    Application.EnableEvents = False

' Unhide all hidden rows
    Columns(1).EntireRow.Hidden = False
    Set HideRange = Range("A1:A" & _
    Range("A65536").End(xlUp).Row)
    
        For Each c In HideRange
            If c.Value = "0" Then c.EntireRow.Hidden = True
        Next c

' Turn Events back on
    Application.EnableEvents = True

End Sub

The sheet module is the one you see when you right click the sheet tab and go to "View Code..."

Am I understanding your requests properly?

 

[jekstein]

yes you are understanding.. but it does not change when i change a value on another sheet (that the module is linked to)

I pasted your code in the worksheet in my Total Sheet which all my other sheets are linked to show their values here.

 

[jekstein]

it's behaving as though there are no macros attached at all

 

[Datsmart]

OK, you are not changing the worksheet the Event code is in, you are changing a different sheet.
Try this:
Create a Worksheet Change event macro in the "other" worksheet module. Past this code in it:

Private Sub Worksheet_Change(ByVal Target As Range)
    Call UpDateTotalSheet
End Sub

Then move the other even code from the "Total Sheet" module and rename it and edit it as below.

Sub UpDateTotalSheet()
Dim HideRange As Range, c As Range 

' Unhide all hidden rows 
    Columns(1).EntireRow.Hidden = False 

    Set HideRange = Range("A1:A" & _ 
    Range("A65536").End(xlUp).Row) 
    
        For Each c In HideRange 
            If c.Value = "0" Then c.EntireRow.Hidden = True 
        Next c 

End Sub

Now, changes in the "other" sheet will fire its' event code, which will call your "UpDateTotalSheet" macro. Make sense? Sound like this is what you need?

 

[jekstein]

guess im not getting it. it doesn't hide them on start up and if i add the hide macro to the workbook_open, it doesn't unhide them if something is added.

So i added your original code to the workbook as an upon open run code so that it hides any rows with a 0 value.

i than added the upon change code to the individual sheets

and the UpdateTotal code to the total sheet.

 

[jekstein]

if you'd like i can send you the sheet..

 

[Datsmart]

Go ahead and send me an email with your sheet attached.
johnr_98541@hotmail.com

 

[Datsmart]

After reviewing jekstein's WorkBook I noted that I had missed an important step. When her WorkSheet Change events occurred and called the UpDateTotalSheet macro, there was no code to Activate that sheet. The macro was trying to hide rows on the calling sheet, not the total sheet.

Edited the code as shown here:

Sub UpDateTotalSheet()
Dim HideRange As Range, c As Range
    
    'Set variable of calling WorkSheet
    Set OldActive = ActiveSheet
    
    'Turn OFF screen updating to stop screen flicker and increase macro speed
    With Application                    'Turn ScreenUpDating & Calculation Off
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

'Make Total Sheet active
Sheets("Total Sheet").Select
' Unhide all hidden rows
    Columns(1).EntireRow.Hidden = False

    Set HideRange = Range("A1:A" & _
    Range("A1000").End(xlUp).Row)
    
        For Each c In HideRange
            If c.Value = "0" Then c.EntireRow.Hidden = True
        Next c
    
    OldActive.Activate  'Return to sheet you came from
    
    With Application              'Turn ScreenUpDating & Calculation ON
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With

End Sub

 

[brianpunx]

Is there a way to do this (i.e. hide the entire row when a paritcular cell's value = 0) by checking more than one cell?

For example, I would like the script to check both cells in columns E and H of a particular row and then hide that row if BOTH cells E and H are = 0.

Can this be done?

Thanks.