Prev: Countif number between x and y
Next: Working with a worksheet that is not the active worksheet
From: jimmy on 14 Apr 2010 04:20
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é
From: Mike H on 14 Apr 2010 04:37
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.


"jimmy" wrote:

> 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é
From: Jacob Skaria on 14 Apr 2010 05:35
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 (MVP - Excel)


"Mike H" wrote:

> 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.
>
>
> "jimmy" wrote:
>
> > 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é
From: Jacob Skaria on 14 Apr 2010 05:41
Mike, sorry my post ended up as a response to your one..which was unintended


"Mike H" wrote:

> 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.
>
>
> "jimmy" wrote:
>
> > 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é
From: Mike H on 14 Apr 2010 05:48
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.


"Jacob Skaria" wrote:

> Mike, sorry my post ended up as a response to your one..which was unintended
>
>
> "Mike H" wrote:
>
> > 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.
> >
> >
> > "jimmy" wrote:
> >
> > > 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é
 |  Next  |  Last
Pages: 1 2
Prev: Countif number between x and y
Next: Working with a worksheet that is not the active worksheet