VBA Else IF statement

[bluegold]

Hi guys, the following macro seems to run a little slow, is there anyway of making the code more efficient / run faster ?


Sub Adjust()
'
' Adjust Macro
'
' Keyboard Shortcut: Ctrl+a
'

Dim lr1 As Long
Dim lr2 As Long

Application.ScreenUpdating = False

Range("A2:AQ" & Range("A" & Rows.Count).End(xlUp).Row).Sort _
Key1:=Range("A2"), Order1:=xlAscending

lr1 = Cells(Rows.Count, "D").End(xlUp).Row
lr2 = Cells(Rows.Count, "AP").End(xlUp).Row

If lr1 < lr2 Then
Range("D2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ElseIf lr1 > lr2 Then
Range("AP2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ElseIf lr1 = lr2 Then
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End If

ActiveSheet.Calculate


Application.ScreenUpdating = False


End Sub

 

[BrianJN1]

The more versed persons here may see something more but I've added a few thoughts.

Sub Adjust()
'
' Adjust Macro
'
' Keyboard Shortcut: Ctrl+a
'

Dim lr1 As Long
Dim lr2 As Long

Application.ScreenUpdating = False
[B][COLOR=#ff0000]Application.Calculation = xlCalculationManual[/COLOR][/B]
Range("A2:AQ" & Range("A" & Rows.Count).End(xlUp).Row).Sort _
Key1:=Range("A2"), Order1:=xlAscending

lr1 = Cells(Rows.Count, "D").End(xlUp).Row
lr2 = Cells(Rows.Count, "AP").End(xlUp).Row

If lr1 < lr2 Then
Range("D2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
ElseIf lr1 > lr2 Then
        Range("AP2").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
    ElseIf lr1 = lr2 Then
    Range("A2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
End If

'ActiveSheet.Calculate  [B][COLOR=#ff0000] I turned this off earlier and back on again below[/COLOR][/B]


Application.ScreenUpdating = [B][COLOR=#800080]True    ' You also had this set to False[/COLOR][/B]
[B][COLOR=#ff0000]Application.Calculation = xlCalculationAutomatic[/COLOR][/B]

End Sub

Does any of that make a difference?

 

[bluegold]

Thanks Brian for your reply, your code did lead me to turning off active calculation and it made a massive difference as my sheet had over 1500 rows of data. I decided I can get away with just doing a full calculation end of day rather than update sheet by sheet all the time.

 

[BrianJN1]

Thanks. Glad I could be of assistance.