PDA

View Full Version : Solved: Hide Rows Where Column A = Zero



Anne Troy
06-23-2004, 02:34 PM
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
06-23-2004, 03:07 PM
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
06-23-2004, 03:15 PM
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
06-23-2004, 03:16 PM
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
06-23-2004, 03:22 PM
No rush! :) Thanks!

NateO
06-23-2004, 03:39 PM
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
06-23-2004, 03:58 PM
That appears to work, Nate. :)
I'll be back.

xXLdev
06-23-2004, 10:08 PM
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
06-24-2004, 02:26 AM
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
07-03-2004, 10:48 AM
Okay, Nate. Would you mind adding your code to the KB? Puhlllleeeeeeeeeeeeze? Whippy and cherry on top!!