D
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
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