Hide Rows Where Column A = Zero

But NOT where Column A is blank!

Code:

---

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?

Here is another way

Code:

---

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

---

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.

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?

No rush! :) Thanks!

Hello Anne,

Quote:

---

Originally Posted by Dreamboat

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:

Code:

---

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.

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

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. :)

Quote:

---

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.

:)

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