Speeding up Hide rows code

[caroline]

Hello,

I am using the following code to hide certain rows. It is perfect when for
100s of rows but is rather slow when I am using 1000s.
"Hide" is in column B for the rows I want to hide.
I need the code to be triggered when the sheet is activated (becauseThe
"Hide" is often conditional and depends on what is happening somewhere else
in the workbook).

Code

' all columns and rows need to be visible to start with for the macros to work
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False

' Search
Dim FirstCell As Range
Dim FoundCell As Range
Dim AllCells As Range
' look for the first matching cell
Set FirstCell = Columns("B:B").Find("Hide", LookIn:=xlValues)

'initialise AllCells
Set AllCells = FirstCell
Set FoundCell = FirstCell
'loop until the FirstCell is found again
Do
Set FoundCell = Columns("B:B").FindNext(After:=FoundCell)
Set AllCells = Union(FoundCell, AllCells)
If FoundCell.Address = FirstCell.Address Then Exit Do
Loop

'select the rows where cells have been found
Dim Arg1 As Range
Set Arg1 = AllCells.EntireRow

Arg1.Select

'hide the rows
Selection.EntireRow.Hidden = True

Columns("A:B").EntireColumn.Hidden = True


any ideas very welcome.
THANKS

 

[Jim Thomlinson]

Hiding and unhiding generates calculation events in th emore current versions
of XL. To that end you can turn it off and back on to stop the recalc.
Additionally get rid of the selects as you really don't need them. Finally
your code will crash if no instance of Hide is found. Try this...

Private Sub Worksheet_Activate()
Dim FirstCell As String
Dim FoundCell As Range
Dim AllCells As Range


Application.Calculation = xlCalculationManual

'all columns and rows need to be visible to start with for the macros to
work
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False

' Search
' look for the first matching cell
Set FoundCell = Columns("B:B").Find(What:="Hide", _
LookIn:=xlValues)

If Not FoundCell Is Nothing Then
'initialise AllCells
Set AllCells = FoundCell
FirstCell = FoundCell.Address
'loop until the FirstCell is found again
Do
Set AllCells = Union(FoundCell, AllCells)
Set FoundCell = Columns("B:B").FindNext(FoundCell)
Loop Until FoundCell.Address = FirstCell
'hide the rows
AllCells.EntireRow.Hidden = True

Columns("A:B").EntireColumn.Hidden = True
End If
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Roger Govier]

Hi Caroline

Could you use something like

Dim ws As Worksheet
Dim test As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
test = "hide" '<=== set to what you want hidden
Set ws = ActiveSheet
If ws.AutoFilterMode = False Then
ws.Range("B1").AutoFilter
End If
If ws.FilterMode Then ws.ShowAllData
Selection.AutoFilter Field:=2, Criteria1:="<>" & test

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True