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

 

[PA HS Teacher]

Are you familiar with Excel's 'Filter' feature.

If I understand you correctly, on Sheet 2, you could enter something like
=And(Len(Sheet1!A1)>0,Sheet1!A1<>0)

Then highlight that column on Sheet2 and Filter for True

 

[Kynan]

Thanks for your prompt reply.

I am familiar with filter however I want to be able to hide rows, not columns. Also I tried that LEN function but it just returns "error".

&(LEN(Bracing!B7>0),Bracing!B7<0)

 

[PA HS Teacher]

I don't totally follow. Usually you use filter to hide rows.

as for the formula, I think you mean:
=And(LEN(Bracing!B7)>0,Bracing!B7<>0)

 

[Kynan]

Haha forget what I said about the "row/column" comment about filtering. I used Filter however I want it to do it automatically. I have uploaded a dummy workboard on the other forum.
http://www.excelforum.com/excel-general/772516-hide-rows-if-value-0-need-help-please.html
Which might explain it a bit better.

Thanks again for your time

 

[Kynan]

Haha was in regards to my poor excel skills and how I forgot about how filtering works. Apologies

 

[Kynan]

I've been playing around with this in excel for quite some time now and still can't get it. Anyone got ideas?

 

[Michael M]

To be honest, you'll have to resort to VBA to Hide rows.
Something like

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 Then
               Sheets("Sheet2").Rows(r).EntireRow.Hidden = True
        End If
    Next r
Application.ScreenUpdating = True
End Sub

 

[Kynan]

I tried copying it into VBA and hitting run but it comes up with "subscript out of range". What am I mean't to do here?

 

[syntaxed]

Check whether the names of the sheets in the code, i.e. "Sheet1" and "Sheet2" match with the names of the sheets in your workbook. If the sheets in your workbook are "Sheet 1" and "Sheet 2", i.e. with a space, then change the names in the code accordingly.