How to write a macro to hide an entire row

[jimmy]

Hello guys,

I would like to write a macro that would hide an entire row if the cells
value in that column is equal to 0.
Someting like this:
A B C
1 20000
2 0 - if c2 = 0 then hide entire row
3 20000


Thank you for your help.

José

 

[Mike H]

Hi,

Try this

Sub Hide_Me()
Set sht = Sheets("Sheet1") ' Change to suit
lastrow = sht.Cells(Cells.Rows.Count, "C").End(xlUp).Row
For x = lastrow To 1 Step -1
If sht.Cells(x, 3).Value <> "" _
And sht.Cells(x, 3).Value = 0 Then
sht.Rows(x).Hidden = True
End If
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Jacob Skaria]

Jimmy, heres another approach which could be used in any sheet to hide rows
with 0 or blank..

Sub HideRows()
Dim lngRow As Long, lngLastRow As Long

lngLastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row

For lngRow = 1 To lngLastRow
If WorksheetFunction.CountIf(Rows(lngRow), 0) + _
WorksheetFunction.CountBlank(Rows(lngRow)) = _
Columns.Count Then Rows(lngRow).Hidden = True
Next

End Sub

 

[Jacob Skaria]

Mike, sorry my post ended up as a response to your one..which was unintended

 

[Mike H]

Jacob,

No worries. I would include a test for a blank cell in your code, the OP
seemed to be specific in wanting to hide rows with zero.

Mike
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

OOPS,

Forgot, congratulations on being appointed MVP, well done and well deserved.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Jacob Skaria]

Thanks. I understand the OP is only looking for zeroes.

 

[jimmy]

Thank you JAcob for your help.
Jimmy

Jimmy, heres another approach which could be used in any sheet to hide rows
with 0 or blank..

Sub HideRows()
Dim lngRow As Long, lngLastRow As Long

lngLastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row

For lngRow = 1 To lngLastRow
If WorksheetFunction.CountIf(Rows(lngRow), 0) + _
WorksheetFunction.CountBlank(Rows(lngRow)) = _
Columns.Count Then Rows(lngRow).Hidden = True
Next

End Sub

 

[jimmy]

Thank you mike for your help.
Jimmy

Hi,

Try this

Sub Hide_Me()
Set sht = Sheets("Sheet1") ' Change to suit
lastrow = sht.Cells(Cells.Rows.Count, "C").End(xlUp).Row
For x = lastrow To 1 Step -1
If sht.Cells(x, 3).Value <> "" _
And sht.Cells(x, 3).Value = 0 Then
sht.Rows(x).Hidden = True
End If
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Copacetic]

Your mistake, as a result of the butterfly effect, is what caused the
entire known universe to collapse on 12-21-2012

Heheheheh

I came back in time to make this post.

It is already too late to do anything about it...

SYHBYLAKYAG

(Stick Your Head Between Your Legs And Kiss Your Ass Goodbye)