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