Macro Speed

[ExcelRoy]

Hi all,

I wonder if i may ask a question

I have a simple macro to hide rows of which i have 4 different, 3 of these work fine on one tab but the other is the same as the first one but on another tab but takes ages to run

Would there be a way to speed this up like the other 3?

Sub Hide_1()
With Rows("13:20016")
.EntireRow.Hidden = Not .EntireRow.Hidden
End With
End Sub

Sub Hide_2()
With Rows("20025:20528")
.EntireRow.Hidden = Not .EntireRow.Hidden
End With
End Sub

Sub Hide_3()
With Rows("20537:20590")
.EntireRow.Hidden = Not .EntireRow.Hidden
End With
End Sub

Sub Hide_4()
With Rows("13:20016")
.EntireRow.Hidden = Not .EntireRow.Hidden
End With
End Sub

Many thanks for any help

 

[mohadin]

Hi
Try like this

Sub test()
    Dim rng As Range
    Set rng = Union(Rows("20537:20590"), Rows("20537:20590"), Rows("20537:20590"), Rows("13:20016"))
     With rng
        .EntireRow.Hidden = Not .EntireRow.Hidden
    End With
End Sub

 

[GWteB]

Sometimes the temporary suppression of some events can boost the performance of macros.
So as an alternative to @mohadin's code ...

Sub ExcelRoy()

    Dim rng     As Range
    Dim bSUpd   As Boolean
    Dim bEvents As Boolean
    Dim bCalc   As XlCalculation

    With Application

        bSUpd = .ScreenUpdating
        bEvents = .EnableEvents
        bCalc = .Calculation
        
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False

        Set rng = .Union(Rows("13:20016"), Rows("20025:20528"), Rows("20537:20590"))
        rng.EntireRow.Hidden = Not rng.EntireRow.Hidden

        .EnableEvents = bEvents
        .Calculation = bCalc
        .Calculate
        .ScreenUpdating = bSUpd

    End With
End Sub

 

[ExcelRoy]

Hi, many thanks for the help, but unfortunately it is still the same in terms of speed?

 

[GWteB]

You're welcome, but I have no more suggestions regarding your issue.
Perhaps the performance difference has to do with the difference in data on one worksheet and the other.