Hide Rows Where Column A = Zero

[Anne Troy]

But NOT where Column A is blank!

Option Explicit
Sub HideRowsWithZeros()
Dim ws As Worksheet
Dim c As Range
Dim rngRange As Range
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
    ws.Select
    Set rngRange = Range(Cells(1, 1), Cells(65336, 1).End(xlUp))
    For Each c In rngRange
        If c.Value = 0 Then
            c.EntireRow.Hidden = True 
        End If
    Next c
Next ws
Application.ScreenUpdating = True
End Sub

And is this the best way to do it?

[xXLdev]

Here is another way

Sub HideRowsWithZeros2()
    Range(Cells(1, 1), Cells(65336, 1).End(xlUp)).Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="0"
    Selection.EntireRow.Delete
End Sub

[Anne Troy]

Sorry. But if the cell say 0.00, then this does not hold. If the VALUE in A1 is zero, but NOT when it is blank.

[xXLdev]

There is a problem with the code that the first row is deleted. That row is deleted also. Also I forgot to turn off AutoFilter. I can fix this later but I need to run out of the office. Maybe someone else can fix it?

[Anne Troy]

No rush! Thanks!

[NateO]

Hello Anne,

Sorry. But if the cell say 0.00, then this does not hold. If the VALUE in A1 is zero, but NOT when it is blank.

As text? Coerce column A's values first then.

Try the following:

Sub HideRowsWithZeros3()
Dim ws As Worksheet
Application.ScreenUpdating = False
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
    With ws
        .AutoFilterMode = False
        With .Range("a1")
              .EntireRow.Hidden = (CBool(Len(.Value)) And .Value = 0)
          End With
          Range(.Cells(1, 1), .Cells(65336, 1).End(xlUp)) _
              .AutoFilter Field:=1, Criteria1:="<>0", _
              visibleDropDown:=False
    End With
Next
Application.ScreenUpdating = True
End Sub
    

 Sub HideRowsWithZeros3()
  Dim ws As Worksheet
  Application.ScreenUpdating = False
  On Error Resume Next
  For Each ws In ThisWorkbook.Worksheets
      With ws
          .AutoFilterMode = False
          With .Range("a1")
              .EntireRow.Hidden = (CBool(Len(.Value)) And .Value = 0)
          End With
          Range(.Cells(1, 1), .Cells(65336, 1).End(xlUp)) _
              .AutoFilter Field:=1, Criteria1:="<>0", _
              visibleDropDown:=False
      End With
  Next
  Application.ScreenUpdating = True
  End Sub

Edit: VBA tags are getting readable on Netscape. The one thing is that it tabs as it thinks I should tab, not as I actually tab.

[Anne Troy]

That appears to work, Nate.
I'll be back.

[xXLdev]

Thanks Nate.

I really need to make sure I have enough time to answer a post completely before I start working on it. Bad me.

[mark007]

Edit: VBA tags are getting readable on Netscape. The one thing is that it tabs as it thinks I should tab, not as I actually tab.

This is by design Nate. Is to make code nicely formatted when it's not been nicely tabbed already. I guess the only difference is the _ extended lines. Might add something in for this.

[Anne Troy]

Okay, Nate. Would you mind adding your code to the KB? Puhlllleeeeeeeeeeeeze? Whippy and cherry on top!!