Speed up macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have noticed the macro below is slow, is there any changes I can make to
speed it up.

Private Sub Worksheet_Activate()

Application.ScreenUpdating = False
Rows.Hidden = False
Set rng = Range("a9:a100")
r1 = rng.Find(".", , xlValues).Row
r2 = rng.Find("Funding Council Grants").Row - 3
Rows(r1 & ":" & r2).Hidden = True
Cells(1, 1).Select
Application.ScreenUpdating = True

End Sub
 
Since I wrote this. You're welcome. I just re-tested and it was instant.
Also, why was it necessary to change from the original? I don't see how
turning calculation off would help but you can try.
Calculation property as it applies to the Application object.
Returns or sets the calculation mode. Read/write XlCalculation.

XlCalculation can be one of these XlCalculation constants.
xlCalculationAutomatic
xlCalculationManual
xlCalculationSemiautomatic

expression.Calculation


Private Sub Worksheet_Activate()
application.calculation=xlcalculationmanual

Rows.Hidden = False
Set rng = Range("a9:a100")

r1 = rng.Find(".", , xlValues).Row
'r1 = rng.Find(".").Row 'original

r2 = rng.Find(UCase("FUNDING COUNCIL GRANTS")).Row - 3
Rows(r1 & ":" & r2).Hidden = True
Cells(1, 1).Select
application.calculation=xlcalculationautomatic
End Sub
 
Thanks for the macro and help

Don Guillett said:
Since I wrote this. You're welcome. I just re-tested and it was instant.
Also, why was it necessary to change from the original? I don't see how
turning calculation off would help but you can try.
Calculation property as it applies to the Application object.
Returns or sets the calculation mode. Read/write XlCalculation.

XlCalculation can be one of these XlCalculation constants.
xlCalculationAutomatic
xlCalculationManual
xlCalculationSemiautomatic

expression.Calculation


Private Sub Worksheet_Activate()
application.calculation=xlcalculationmanual

Rows.Hidden = False
Set rng = Range("a9:a100")

r1 = rng.Find(".", , xlValues).Row
'r1 = rng.Find(".").Row 'original

r2 = rng.Find(UCase("FUNDING COUNCIL GRANTS")).Row - 3
Rows(r1 & ":" & r2).Hidden = True
Cells(1, 1).Select
application.calculation=xlcalculationautomatic
End Sub
Click to expand...
 
Back
Top