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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,383
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. 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,446
Office Version
  1. 365
  2. 2010
Platform
  1. 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,278
Messages
5,657,816
Members
418,414
Latest member
ECMdusty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top