Cell Formats & Hiding
 

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!"

Cell Formats & Hiding
 

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!"
.

Cell Formats & Hiding
 

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!"

Cell Formats & Hiding
 

On Mar 21, 11:22*pm, Greendistantstar
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.