Prev: Help with conditional formatting...
Next: How to get repeatable Excel RAND sequence?
From: Greendistantstar on 21 Mar 2010 02:37
Hi

I have a worksheet I use frequently, where some cells have zero values.

For presentation's sake, I hide rows where the value is zero, and this I do manually.

The zero vales can and do change.

How do I write a macro to hide cells with zero values?

TIA

GDS


"Let's roll!"
From: Gary''s Student on 21 Mar 2010 10:50
Give this a try:

Sub HideZeroRows()
Dim r As Range, nLastRow As Long, r2 As Range
Dim n1 As Long, n2 As Long
Dim f As WorksheetFunction
Set f = Application.WorksheetFunction
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
Cells.EntireRow.Hidden = False
For i = 1 To nLastRow
Set r2 = Rows(i)
n1 = f.CountIf(r2, 0) + f.CountIf(r2, "")
If n1 = Columns.Count Then
Rows(i).Hidden = True
End If
Next
End Sub

--
Gary''s Student - gsnu201001


"Greendistantstar" wrote:

> Hi
>
> I have a worksheet I use frequently, where some cells have zero values.
>
> For presentation's sake, I hide rows where the value is zero, and this I do manually.
>
> The zero vales can and do change.
>
> How do I write a macro to hide cells with zero values?
>
> TIA
>
> GDS
>
>
> "Let's roll!"
> .
>
From: Greendistantstar on 21 Mar 2010 11:22
Gary''s Student wrote:
> Give this a try:
>
> Sub HideZeroRows()
> Dim r As Range, nLastRow As Long, r2 As Range
> Dim n1 As Long, n2 As Long
> Dim f As WorksheetFunction
> Set f = Application.WorksheetFunction
> Set r = ActiveSheet.UsedRange
> nLastRow = r.Rows.Count + r.Row - 1
> Cells.EntireRow.Hidden = False
> For i = 1 To nLastRow
> Set r2 = Rows(i)
> n1 = f.CountIf(r2, 0) + f.CountIf(r2, "")
> If n1 = Columns.Count Then
> Rows(i).Hidden = True
> End If
> Next
> End Sub

Thanks. I'll trying running this later today.

GDS

"Let's roll!"
From: minyeh on 22 Mar 2010 04:18
On Mar 21, 11:22 pm, Greendistantstar <Greendistants...(a)iinet.net.au>
wrote:
> Gary''s Student wrote:
> > Give this a try:
>
> > Sub HideZeroRows()
> > Dim r As Range, nLastRow As Long, r2 As Range
> > Dim n1 As Long, n2 As Long
> > Dim f As WorksheetFunction
> > Set f = Application.WorksheetFunction
> > Set r = ActiveSheet.UsedRange
> > nLastRow = r.Rows.Count + r.Row - 1
> > Cells.EntireRow.Hidden = False
> > For i = 1 To nLastRow
> >     Set r2 = Rows(i)
> >     n1 = f.CountIf(r2, 0) + f.CountIf(r2, "")
> >     If n1 = Columns.Count Then
> >            Rows(i).Hidden = True
> >     End If
> > Next
> > End Sub
>
> Thanks. I'll trying running this later today.
>
> GDS
>
> "Let's roll!"- Hide quoted text -
>
> - Show quoted text -

i'll prefer using autofilter function.
 | 
Pages: 1
Prev: Help with conditional formatting...
Next: How to get repeatable Excel RAND sequence?