Hiding Rows-Macro or Autofilter

[djcmisc]

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?

 

[Norman Jones]

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
'<<================

 

[Norman Jones]

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
'<<================

 

[Tim Zych]

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

 

[djcmisc]

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







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