Microsoft Office Forums - View Single Post

macropod · 05-08-2013, 05:51 AM

Hint: Your 'Macro1' sub could be reduced to:

Code:

Sub Macro1()
Application.ScreenUpdating = False
Dim lnN As Integer
With Range("SummerCompTable")
    For lnN = 1 To .ListObject.ListRows.Count
        Select Case .Cells(lnN, 4).Value
        Case "Both"
            Call InsertLine("POYCompTable", lnN)
            Call InsertLine("ScratchCompTable", lnN)
        Case "POY"
            Call InsertLine("POYCompTable", lnN)
        Case "Scratch"
            Call InsertLine("ScratchCompTable", lnN)
        Case Else
            MsgBox "Data error in 'Where' column"
            Exit Sub
        End Select
    Next lnN
End With
Application.ScreenUpdating = True
End Sub

The essential logic difference between your code and the code I've posted here and previously is that nothing gets selected. Apart from the fact it means the worksheet doesn't even need to be activated (and, hence, your active cell need never change) when you run the code, working with range objects directly is much faster. Disabling screen updating while the code is running nets even greater performance increases.