Sort data very slow

ManvinderKaur

Board Regular
Joined
Jun 16, 2010
Messages
149
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
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,116
Office Version
2013
Platform
Windows
Try this to speed it up a bit
Code:
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

Board Regular
Joined
Jun 16, 2010
Messages
149
This code is slow becuase I am using
Code:
 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

MrExcel MVP
Joined
May 26, 2009
Messages
17,082
Office Version
365, 2010
Platform
Windows
This code is slow becuase I am using
Code:
 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:
Code:
Application.calculation = xlCalculationManual
at the start of your code and:
Code:
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,534
Messages
5,511,881
Members
408,867
Latest member
Ranjeet yamgekar

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top