Automatically hide rows with 0 value....faster?

[Dan Brimley]

Hi,

I used this code in VB to automatically hide rows that have formulas
resulting in a value of zero:

Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas)
If cell.Text = "" Or cell.Value = 0 Then _
cell.EntireRow.Hidden = True
Next cell
End With
stoppit:
Application.EnableEvents = True
End Sub

Then I decided that I would rather have it so I can hide or unhide manually,
so I created macros with hide and show buttons with this code:
Sub Hide()
On Error GoTo stoppit
Application.EnableEvents = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In .Columns(30).SpecialCells(xlCellTypeFormulas)
If cell.Text = "" Or cell.Value = 0 Then _
cell.EntireRow.Hidden = True
Next cell
End With
stoppit:
Application.EnableEvents = True
End Sub
------------------------------
Sub Show()
On Error GoTo stoppit
Application.EnableEvents = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In .Columns(30).SpecialCells(xlCellTypeFormulas)
If cell.Text = "" Or cell.Value = 0 Then _
cell.EntireRow.Hidden = False
Next cell
End With
stoppit:
Application.EnableEvents = True
End Sub

With the macros, this runs slower than desired. Is there a way to keep it
in VB and still allow a way to use the hide / show buttons, so it's not
automatic? It runs much faster thru VB.

Thanks,
Dan

 

[Don Guillett]

Filtering should be faster.

Sub hidezero()
Lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:a" & Lr).AutoFilter Field:=1, Criteria1:="<>0"
'Range("A1:a" & Lr).AutoFilter' to unfilter
End Sub

 

[Dan Brimley]

Thanks. Does this give me the ability to manually filter or unfilter the
zeros? How would I do that? Also, if a value is less than zero, I don't
want to filter it. I forgot to mention that in my original post.

Thanks

 

[Don Guillett]

If you want to filter out <0 and 0 just change
:="<>0"
to
:=">0"

Of course you can still manually use the filter. To manually unfilter
data>filter>autofilter
or put the last line in a separate macro

 

[Dan Brimley]

Great, that will work. Thanks for your help!

If you want to filter out <0 and 0 just change
:="<>0"
to
:=">0"

Of course you can still manually use the filter. To manually unfilter
data>filter>autofilter
or put the last line in a separate macro

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Glad to help