simple task makes macro slow

Question

I am trying to sort some cells and then center and bold the afterwords added header of my list and the process is very slow. This is the end of a working macro and I see that it has gotten much slower after I added the bellow code:

Columns("A:F").Select
ActiveWorkbook.Worksheets("search results").sort.SortFields.Clear
ActiveWorkbook.Worksheets("search results").sort.SortFields.Add Key:=Range( _
    "E2:E112"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("search results").sort
    .SetRange Range("A1:F112")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Worksheets("search results").Activate
Range("A1").EntireRow.Insert

Range("A1") = "A/A"
Range("B1") = "Tag"
Range("C1") = "Work"
Range("D1") = "Ôýðïò"
Range("E1") = "Date"
Range("F1") = "Remark"


Worksheets("search results").Range("A1:F1").Font.Bold = True
Worksheets("search results").Range("A1:F1").HorizontalAlignment =xlCenter

Worksheets("search results").Activate
Range("A1").Activate

Is there something wrong or a way to do this more effective ? Somethimes the programm is not even responding and I have to close and restart.

I don't see anything particular in the code posted above that can potentially slow it down. It could be something else e.g. `1. are there multiple formulae on this sheet and their re-calculation taking time.` Or `2. Some sheet based event codes like Worksheet_change` which are getting fired with this. — shrivallabha.redij, Oct 23 '17 at 06:37
If macro is used multiple times, activating a worksheet or selecting range can slow it down. You can you reference worksheet by `With Worksheets("search results")` ... `End With` instead of activating it, though I doubt it is the issue. Try turning off .ScreenUpdating, DisplayStatusBar, .EnableEvents, setting .Calculation to xlCalculationManual and removing page breaks from each Worksheet. It will give perfomance a slight boost. — AntiDrondert, Oct 23 '17 at 06:48

score 0 · Answer 1 · answered Oct 23 '17 at 07:44

You can try to turn Screen Updating, Events and Calculation off at the beginning of your script, because the Filter Statement can be Really slow. You can take a look at Optimizing VBA macro for Informations how to Optimate your Scripts and increase the Performance.

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Columns("A:F").Select

With ActiveWorkbook.Worksheets("search results")
   .Sort.SortFields.Clear
   .Sort.SortFields.Add Key:=Range( _
    "E2:E112"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
End with


With ActiveWorkbook.Worksheets("search results").Sort
    .SetRange Range("A1:F112")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

With Worksheets("search results")
    .Range("A1").EntireRow.Insert
    .Range("A1") = "A/A"
    .Range("B1") = "Tag"
    .Range("C1") = "Work"
    .Range("D1") = "Ôýðïò"
    .Range("E1") = "Date"
    .Range("F1") = "Remark"
    .Range("A1:F1").Font.Bold = True
    .Range("A1:F1").HorizontalAlignment = xlCenter
    .Range("A1").Activate
End With

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

there may be a problem with the sort. ... you are sorting A1:F112 and you specify a header, so the first row never gets sorted. if the first row contains data, then insert the header row first and then sort A1:F113 .... or do the initial sort without a header — jsotola, Oct 23 '17 at 07:58
Yes that's rigth. But that wasn't asked, maybe he need it this way. — Moosli, Oct 23 '17 at 08:00
the comment was meant for the OP. i should have included @user36510 — jsotola, Oct 23 '17 at 08:10

simple task makes macro slow

1 Answers1