Speeding up Hide rows code
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
--
HTH...
Jim Thomlinson
"caroline" wrote:
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
--
caroline
|