Hide Rows if Value = 0 (Need Help Please)

[Kynan]

Alright well I'm trying to find a way to hide cells on "Sheet 2" if values on "Sheet 1" = 0 (or are blank) and keep the rows that do have values.

I've been looking around to try and find a formula, like a
=IF(VALUE=0 HIDE ROW, VALUE>0 KEEP ROW)
(Yes I know that won't work but that's the concept I'm trying to achieve)

However I've only come across suggestions/codes using "Macros"/VBA. I was trying to avoid Macros/VBA because I have no idea how any of that works.

Does anyone know of a code that will help me achieve what I want?
(I know how to "view code" and that's pretty much it so I might need instructions on what to do after pasting the code in)

Apologies for being a newbie but thank you in advance



This question is also posted on another forum so I can hopefully get a fast response -
http://www.excelforum.com/excel-gen...-if-value-0-need-help-please.html#post2509346

 

[Kynan]

Alright so I changed it to the actual names and I get past that error, however its not hiding any data I want it to and it's hiding everything above it (which I need). Any ideas?

 

[syntaxed]

If I understand correctly, what the code does is check column A values in Sheet 1, and if the value in any row is equal to 0, then it hides the same row of Sheet 2. Example: cell A4 of Sheet 1 equals 0. Then row 4 of Sheet 2 is hidden.
Is this not what you are looking for?

 

[Michael M]

Thanks for responding Syntaxed
I haven't had Net access most of the day, and yes, you are correct in your explanation.
Kynan, I believe that's what you asked for. If you need to make adjustments to columns or arguments, please repost. If you are not certain whether the cells are 0 or blank, this might cover both options

Sub hiderow()
Dim lr As Long, r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = lr To 2 Step -1
        If Sheets("Sheet1").Range("A" & r).Value = 0 OR Sheets("Sheet1").Range("A" & r).Value = "" Then
               Sheets("Sheet2").Rows(r).EntireRow.Hidden = True
        End If
    Next r
Application.ScreenUpdating = True
End Sub

Also, you stated

however its not hiding any data I want it to and it's hiding everything above it

Have you made sure your data is aligned the same on each sheet.....row 4 on sheet1 should refer to row 4 on sheet2 !!

 

[excel-lerate]

Michael - how would you change the VBA code if the change applied to one particular row?

 

[excel-lerate]

For Instance if E3 = 0 , then hide row. Otherwise, no change to the values in this row.

 

[Michael M]

Sub hiderow()
    If Sheets("Sheet1").Range("E3").Value = 0 Or Sheets("Sheet1").Range("E3").Value = "" Then
           Sheets("Sheet2").Rows(3).EntireRow.Hidden = True
    End If
End Sub

 

[excel-lerate]

thanks Michael