How to hide rows/columns that have zero values

  • Thread starter Thread starter Dave
  • Start date Start date
Hi
try something like the following:
Sub hide_rows()
Dim RowNdx As Long
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").value=0 Then
Rows(RowNdx).hidden = True
End If
Next RowNdx
End Sub
 
Frank,

It worked, thanks. Is there a way to insert logic that
would only hide columns that actually show zero values and
not hide empty rows? I am in the process of applying this
logic to an Income Statement and I have empty rows under
each of my Titles before my data is listed. I would like
to hide all rows that contain zero balances but not the
others that are intentionally left blank. Does that make
 
Hi
replace
If Cells(RowNdx, "A").value=0 Then
with
If Cells(RowNdx, "A").value=0 and
Cells(RowNdx, "A").value=<>"" Then
 
Sorry Frank,

I copied your new text and I keep getting a syntax error.
Here's what I have:

Sub hide_rows()
Dim RowNdx As Long
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").value=0 and
Cells(RowNdx, "A").value=<>"" Then
Rows(RowNdx).Hidden = True
End If
Next RowNdx
End Sub
 
Hi
sorry, some typos. Try:
Sub hide_rows()
Dim RowNdx As Long
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = 0 And _
Cells(RowNdx, "A").Value <> "" Then
Rows(RowNdx).Hidden = True
End If
Next RowNdx
End Sub
 
Dave,

Sub Hide_Rows()

Dim RNG As Range
Dim CL As Range
Dim R As Long

On Error GoTo e:

Set RNG = Cells(Application.Match(0, Range("A:A"), 0), "A")
R = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For Each CL In Range("A1:A" & R)
If CL.Value = 0 And CL.Value <> "" Then
Set RNG = Application.Union(RNG, CL)
End If
Next

RNG.EntireRow.Hidden = True
e:

End Sub


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--
 
Back
Top