Sort data very slow

[ManvinderKaur]

Hi I have four sheets
Baseline, reassessment, comparision and summary
I sort baseline as well as reassessment each time all sheets activate I am using following code

Public Sub BaselineSortAsc()
Dim LastRow As Long   'This is the LAST Non Empty Row
Dim LastRowCount As Long
With Sheets("Baseline")
    .Unprotect
    LastRow = .Range("A" & Rows.count).End(xlUp).Row 'This is the LAST Non Empty Row
    For LastRowCount = 4 To LastRow
    .Range("A" & LastRowCount).Value = Trim(.Range("A" & LastRowCount).Value)
    Next
   .Range("A3:AZ" & LastRow).Sort Key1:=.Range("A3"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    .Protect
End With
End Sub
 
Public Sub ReassessmentSortAsc()
Dim LastRow As Long   'This is the LAST Non Empty Row
Dim LastRowCount As Long
With Sheets("Re-assessment")
    .Unprotect
    LastRow = .Range("A" & Rows.count).End(xlUp).Row 'This is the LAST Non Empty Row
    For LastRowCount = 4 To LastRow
    .Range("A" & LastRowCount).Value = Trim(.Range("A" & LastRowCount).Value)
    Next
    .Range("A3:AZ" & LastRow).Sort Key1:=.Range("A3"), Order1:=xlAscending, Key2:=.Range("F3"), Order2:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
    .Protect
End With
End Sub

each time I activate sheet. That took 1 min to load. Is their anything wrong in code or it is always slow. I have 200 rows in baseline and reass. I have first four lines as header lines.
your help whould be greatly appreciated

 

[Michael M]

Try this to speed it up a bit

Public Sub BaselineSortAsc()
Dim LastRow As Long   'This is the LAST Non Empty Row
Dim LastRowCount As Long
application.screenupdating=False
With Sheets("Baseline")
    .Unprotect
    LastRow = .Range("A" & Rows.count).End(xlUp).Row 'This is the LAST Non Empty Row
    For LastRowCount = 4 To LastRow
    .Range("A" & LastRowCount).Value = Trim(.Range("A" & LastRowCount).Value)
    Next
   .Range("A3:AZ" & LastRow).Sort Key1:=.Range("A3"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    .Protect
End With
application.screenupdating=True
End Sub
 
Public Sub ReassessmentSortAsc()
Dim LastRow As Long   'This is the LAST Non Empty Row
Dim LastRowCount As Long
application.screenupdating=False
With Sheets("Re-assessment")
    .Unprotect
    LastRow = .Range("A" & Rows.count).End(xlUp).Row 'This is the LAST Non Empty Row
    For LastRowCount = 4 To LastRow
    .Range("A" & LastRowCount).Value = Trim(.Range("A" & LastRowCount).Value)
    Next
    .Range("A3:AZ" & LastRow).Sort Key1:=.Range("A3"), Order1:=xlAscending, Key2:=.Range("F3"), Order2:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
    .Protect
End With
application.screenupdating=True
End Sub

Screenupdating lines added in each sub

 

[ManvinderKaur]

This code is slow becuase I am using

 For LastRowCount = 4 To LastRow
    .Range("A" & LastRowCount).Value = Trim(.Range("A" & LastRowCount).Value)
    Next

is there any way I can trim value when people enter value instead of running whole loop again and again

 

[JoeMo]

This code is slow becuase I am using

 For LastRowCount = 4 To LastRow
    .Range("A" & LastRowCount).Value = Trim(.Range("A" & LastRowCount).Value)
    Next

is there any way I can trim value when people enter value instead of running whole loop again and again

You could put a helper cell next to the entry cell (say that's A1) and use:
=TRIM(A1) and process this column for the sorting. Alternatively, you should try adding:

Application.calculation = xlCalculationManual

at the start of your code and:

Application.calculation = xlCalculationAutomatic

just before the end sub line. This will prevent a time consuming calculation from happening each time you invoke the trim function in your code.