Hiding Rows-Macro or Autofilter
 

I am trying to determine the best way to hide rows based on a certain
criterial. In sheet 1 of my model, I have a column of cells E63:E102
with formulas that either equal zero, or a value greater than zero. I
would like to hide the rows where the value is equal to zero. I have
used a macro from other worksheets but It does not seem to be working-
not even slowly- in this model. I think maybe my file size is too
large? (almost 4M). The macro I have been currently using is,
Dim c As Range
For Each c In Range("e63", Range("e102").End(xlUp))
If c.value = 0 Then
c.EntireRow.Hidden = True
Else: c.EntireRow.Hidden = False
End If
Next c
End Sub

I am not sure if this is the best one to be using. If my results are
slow, should I switch to an autofilter macro. I would like to have it
as a macro so I can just refresh the range as numbers get added or
deleted with a click of a button. Any suggestions?

Hiding Rows-Macro or Autofilter
 

Hi DJ,

You could increase the speed of your macro
by temporarily turning of Excel's events and
calculatrions and restoring these settings at the
end of your macro.

Therefore, perhaps try something like:

'================
Private Sub CommandButton1_Click()
Dim Rng As Range
Dim rCell As Range
Dim CalcMode As Long
Dim ViewMode As Long

Set Rng = Me.Range("E63", Range("E102").End(xlUp))

On Error GoTo XIT
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rCell In Rng.Cells
With rCell
rCell.EntireRow.Hidden = .Value = 0
End With
Next rCell

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<================


---
Regards.
Norman
wrote in message
...
I am trying to determine the best way to hide rows based on a certain
criterial. In sheet 1 of my model, I have a column of cells E63:E102
with formulas that either equal zero, or a value greater than zero. I
would like to hide the rows where the value is equal to zero. I have
used a macro from other worksheets but It does not seem to be working-
not even slowly- in this model. I think maybe my file size is too
large? (almost 4M). The macro I have been currently using is,
Dim c As Range
For Each c In Range("e63", Range("e102").End(xlUp))
If c.value = 0 Then
c.EntireRow.Hidden = True
Else: c.EntireRow.Hidden = False
End If
Next c
End Sub

I am not sure if this is the best one to be using. If my results are
slow, should I switch to an autofilter macro. I would like to have it
as a macro so I can just refresh the range as numbers get added or
deleted with a click of a button. Any suggestions?

Hiding Rows-Macro or Autofilter
 

Hi DJ,

And better still might be:

'================
Private Sub CommandButton1_Click()
Dim Rng As Range
Dim rCell As Range
Dim CalcMode As Long
Dim ViewMode As Long

Set Rng = Me.Range("E63", Range("E102").End(xlUp))

On Error GoTo XIT
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
Me.DisplayPageBreaks = False


For Each rCell In Rng.Cells
With rCell
rCell.EntireRow.Hidden = .Value = 0
End With
Next rCell

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

ActiveWindow.View = ViewMode

End Sub
'<<================



---
Regards.
Norman

Hiding Rows-Macro or Autofilter
 

One thing to be aware of in your macro: End(xlDirection) bypasses data in
hidden rows or columns.

This modification unhides everything and then re-hides the cells with 0. To
help with performance and debugging, I like to build one big range and at
the end of the evaluation, hide the rows in a single action.

Sub HideStuff()
Dim c As Range, rngToHide As Range, rngToLookat As Range, lngCalc As
Long
Set rngToLookat = Range("E63:E102")
rngToLookat.EntireRow.Hidden = False
For Each c In rngToLookat.Cells
If c.Value = 0 Then
If rngToHide Is Nothing Then
Set rngToHide = c
Else
Set rngToHide = Union(rngToHide, c)
End If
End If
Next c
If Not rngToHide Is Nothing Then
lngCalc = Application.Calculation
Application.Calculation = xlCalculationManual
rngToHide.EntireRow.Hidden = True
Application.Calculation = lngCalc
End If
End Sub

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

wrote in message
...
I am trying to determine the best way to hide rows based on a certain
criterial. In sheet 1 of my model, I have a column of cells E63:E102
with formulas that either equal zero, or a value greater than zero. I
would like to hide the rows where the value is equal to zero. I have
used a macro from other worksheets but It does not seem to be working-
not even slowly- in this model. I think maybe my file size is too
large? (almost 4M). The macro I have been currently using is,
Dim c As Range
For Each c In Range("e63", Range("e102").End(xlUp))
If c.value = 0 Then
c.EntireRow.Hidden = True
Else: c.EntireRow.Hidden = False
End If
Next c
End Sub

I am not sure if this is the best one to be using. If my results are
slow, should I switch to an autofilter macro. I would like to have it
as a macro so I can just refresh the range as numbers get added or
deleted with a click of a button. Any suggestions?

Hiding Rows-Macro or Autofilter
 

On May 28, 10:53*pm, "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote:
One thing to be aware of in your macro: End(xlDirection) bypasses data in
hidden rows or columns.

This modification unhides everything and then re-hides the cells with 0. To
help with performance and debugging, I like to build one big range and at
the end of the evaluation, hide the rows in a single action.

Sub HideStuff()
* * Dim c As Range, rngToHide As Range, rngToLookat As Range, lngCalc As
Long
* * Set rngToLookat = Range("E63:E102")
* * rngToLookat.EntireRow.Hidden = False
* * For Each c In rngToLookat.Cells
* * * * If c.Value = 0 Then
* * * * * * If rngToHide Is Nothing Then
* * * * * * * * Set rngToHide = c
* * * * * * Else
* * * * * * * * Set rngToHide = Union(rngToHide, c)
* * * * * * End If
* * * * End If
* * Next c
* * If Not rngToHide Is Nothing Then
* * * * lngCalc = Application.Calculation
* * * * Application.Calculation = xlCalculationManual
* * * * rngToHide.EntireRow.Hidden = True
* * * * Application.Calculation = lngCalc
* * End If
End Sub

--
Tim Zychwww.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

wrote in message

...



I am trying to determine the best way to hide rows based on a certain
criterial. *In sheet 1 of my model, I have a column of cells E63:E102
with formulas that either equal zero, or a value greater than zero. I
would like to hide the rows where the value is equal to zero. I have
used a macro from other worksheets but It does not seem to be working-
not even slowly- in this model. I think maybe my file size is too
large? (almost 4M). *The macro I have been currently using is,
Dim c * * *As Range
* *For Each c In Range("e63", Range("e102").End(xlUp))
* * * *If c.value = 0 Then
* * * * * *c.EntireRow.Hidden = True
* * * *Else: c.EntireRow.Hidden = False
* * * *End If
* *Next c
End Sub

I am not sure if this is the best one to be using. If my results are
slow, should I switch to an autofilter macro. I would like to have it
as a macro so I can just refresh the range as numbers get added or
deleted with a click of a button. Any suggestions?- Hide quoted text -

- Show quoted text -

Hi Tim, Thank you for the response. It seems to be working well and
seems pretty efficient given my file size. Thanks again!