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

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

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan Brimley" wrote in message
...
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

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

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
datafilterautofilter
or put the last line in a separate macro

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan Brimley" wrote in message
...
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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan Brimley" wrote in message
...
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

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

Great, that will work. Thanks for your help!


"Don Guillett" wrote in message
...
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
datafilterautofilter
or put the last line in a separate macro

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan Brimley" wrote in message
...
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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan Brimley" wrote in message
...
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

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

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan Brimley" wrote in message
...
Great, that will work. Thanks for your help!


"Don Guillett" wrote in message
...
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
datafilterautofilter
or put the last line in a separate macro

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan Brimley" wrote in message
...
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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan Brimley" wrote in message
...
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