Macro takes to long any Ideas how to speed it up

[sdoppke]

Hi everyone, anyone have an idea how to make this run faster?

Sub From_ScheduleDashboard_to_ScheduleTool()
Application.ScreenUpdating = False
Sheets("Schedule Tool").Visible = True
Sheets("Schedule Tool").Select
Rows("240:1197").Select
    Selection.EntireRow.Hidden = True
Rows("3:239").Select
    Selection.EntireRow.Hidden = False
Sheets("Schedule Tool").Range("A1") = "Now Showing: Week 1"
Sheets("Schedule Tool").Range("A2") = Sheets("Labor Budget").Range("B1")
Dim cell As Range
    Dim myLastRow As Long
    myLastRow = Cells(Rows.Count, "CK").End(xlUp).row
    For Each cell In Range("CK5:CK" & myLastRow)
        If (cell.Value = "P") Or (cell.Value = "O") Then
            cell.EntireRow.Hidden = True
        End If
    Next
    myLastRow = Cells(Rows.Count, "A").End(xlUp).row
    For Each cell In Range("A1:A" & myLastRow)
        If (cell.Value = " ") And (cell.Formula <> 0) Or Len(cell) = 1 Or Len(cell) = 2 Then
            cell.EntireRow.Hidden = True
        End If
    Next
Range("F5").Select
Application.ScreenUpdating = True
End Sub

thanks a ton for taking a look and helping out.

sd

 

[Michael M]

Hi I think you'd be better off hiding rows from the bottom up
try this code, untested ( haven't got Excel at the moment)

Sub From_ScheduleDashboard_to_ScheduleTool()
Application.ScreenUpdating = False
Sheets("Schedule Tool").Visible = True
Sheets("Schedule Tool").Rows("240:1197").EntireRow.Hidden = True
Rows("3:239").EntireRow.Hidden = False
Sheets("Schedule Tool").Range("A1") = "Now Showing: Week 1"
Sheets("Schedule Tool").Range("A2") = Sheets("Labor Budget").Range("B1")
Dim r As Long
    Dim myLastRow As Long
    myLastRow = Cells(Rows.Count, "CK").End(xlUp).Row
    For r = myLastRow To 5 Step -1
        If Range("CK" & r).Value = "P" Or Range("CK" & r).Value = "O" Then
            Rows(r).EntireRow.Hidden = True
        End If
    Next r
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For r = myLastRow To 1 Step -1
        If Range("A" & r).Value = " " And Range("A" & r).Formula <> 0 Or Len(Range("A" & r)) = 1 Or Len(Range("A" & r)) = 2 Then
            Rows(r).EntireRow.Hidden = True
        End If
    Next
Range("F5").Select
Application.ScreenUpdating = True
End Sub

 

[sdoppke]

Hi I think you'd be better off hiding rows from the bottom up
try this code, untested ( haven't got Excel at the moment)

Sub From_ScheduleDashboard_to_ScheduleTool()
Application.ScreenUpdating = False
Sheets("Schedule Tool").Visible = True
Sheets("Schedule Tool").Rows("240:1197").EntireRow.Hidden = True
Rows("3:239").EntireRow.Hidden = False
Sheets("Schedule Tool").Range("A1") = "Now Showing: Week 1"
Sheets("Schedule Tool").Range("A2") = Sheets("Labor Budget").Range("B1")
Dim r As Long
    Dim myLastRow As Long
    myLastRow = Cells(Rows.Count, "CK").End(xlUp).Row
    For r = myLastRow To 5 Step -1
        If Range("CK" & r).Value = "P" Or Range("CK" & r).Value = "O" Then
            Rows(r).EntireRow.Hidden = True
        End If
    Next r
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For r = myLastRow To 1 Step -1
        If Range("A" & r).Value = " " And Range("A" & r).Formula <> 0 Or Len(Range("A" & r)) = 1 Or Len(Range("A" & r)) = 2 Then
            Rows(r).EntireRow.Hidden = True
        End If
    Next
Range("F5").Select
Application.ScreenUpdating = True
End Sub

WOW!! that worked much faster!!! thanks a ton, on that note. Can you recommend a way to identify code that can be sped up. Or is just doing what i just did (using the forum) the best way?

sd

 

[p45cal]

The following gave a 3x increase in speed here:

Sub From_ScheduleDashboard_to_ScheduleTool()
Dim rngToHide As Range
Dim cll As Range
Dim myLastRow As Long
Application.ScreenUpdating = False
With Sheets("Schedule Tool")
    .Visible = True
    .Rows("240:1197").EntireRow.Hidden = True
    .Rows("3:239").EntireRow.Hidden = False
    .Range("A1") = "Now Showing: Week 1"
    .Range("A2") = Sheets("Labor Budget").Range("B1")
    myLastRow = .Cells(.Rows.Count, "CK").End(xlUp).Row
    For Each cll In .Range("CK5:CK" & myLastRow).Cells
        If cll.Value = "P" Or cll.Value = "O" Then Set rngToHide = Union(cll, IIf(rngToHide Is Nothing, cll, rngToHide))
    Next
    myLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For Each cll In .Range("A1:A" & myLastRow).Cells
        If (cll.Value = " ") And (cll.Formula <> 0) Or Len(cll) = 1 Or Len(cll) = 2 Then Set rngToHide = Union(cll, IIf(rngToHide Is Nothing, cll, rngToHide))
    Next
    If Not rngToHide Is Nothing Then rngToHide.EntireRow.Hidden = True
    Application.Goto .Range("F5")
End With    'Sheets("Schedule Tool")
Application.ScreenUpdating = True
End Sub

It tries to keep code operations on the sheet to a minimum, storing up all conditional hiding to one line (executed only once) towards the end, removing all selecting, and not requiring the sheet even to be the active until the very end where you select cell F5