how can i optimize my code vba so , it can run effectively?

Question

i have tried to write a code that sort and merge the cells with the same value and add headlines to each columns but it execute after 5min of loading and sometimes the program freezes until i restart it here it is my code .

ps: format macro calls merge_same_cells macro

 Sub Merge_Same_Cells()

Application.DisplayAlerts = False

Dim rng As Range

MergeCells:

For Each rng In Selection

If rng.Value = rng.Offset(1, 0).Value And rng.Value <> "" Then
Range(rng, rng.Offset(1, 0)).Merge
Range(rng, rng.Offset(1, 0)).HorizontalAlignment = xlCenter
Range(rng, rng.Offset(1, 0)).VerticalAlignment = xlCenter
GoTo MergeCells
End If
Next


End Sub

Sub format()
Dim var As String
var = Application.InputBox(prompt:="nom du sheet")
If FeuilleExiste(var) = False Then
MsgBox "sheet doesn't exist"
Else
Range("B6") = "pannes"
Range("C6") = "pannes abrv"
Range("D6") = "nobmre"
Range("B6:D6").AutoFilter
ActiveWorkbook.Worksheets(var).AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(var).AutoFilter.Sort.SortFields.Add Key:= _
        Range("B6"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets(var).AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Range("D:D").Select
   Selection.SpecialCells(xlCellTypeConstants, 23).Select
Call Merge_Same_Cells
End If

End Sub

Without looking too closely I'd suggest reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Does the selection used in `Merge_Same_Cells` ever result in the whole sheet/all rows being selected? The loop will look at all rows then even if it doesn't act on them and your `GoTo MergeCells` might sometimes start the whole loop again. Definitely look at avoiding `Select` though. — Darren Bartrup-Cook, May 11 '22 at 13:12
Does `Selection.SpecialCells(xlCellTypeConstants, 23).Select` always select a contiguous range(with no gaps)? If not then your `Merge_Same_Cells` may not be doing what you want. — Tim Williams, May 11 '22 at 17:22

score 0 · Answer 1 · answered May 11 '22 at 12:49

The first thing I would recommend is using the Timer function with the Debug.Print method to determine which parts of your code are taking the most time — no point in taking an hour to try and optimise a part that takes 0.5 seconds, while ignoring a part that takes 5 minutes! This will also help you to make your question more specific — i.e. “How do I make this specific bit of code run faster?”, rather than “How do I make this entire function/program run faster?”

However, 2 things that are known to speed up functions in many cases are the Application.Calculation property (i.e. should Excel recalculate the entire workbook every time a cell changes), and the Application.ScreenUpdating property (i.e. should the graphics card be redrawing the worksheet every time a cell changes). It can be beneficial to set these to "nope" before running certain code (Application.Calculation=xlCalculationManual — which allows you to use [Worksheet].Calculate or [Range].Calculate to recalculate specific sheets/cells when you want/need — and Application.ScreenUpdating=False), but remember to restore them after the code has run! (Application.Calculation=xlCalculationAutomatic and Application.ScreenUpdating=True)

Also, when running a large loop that can't be optimised further, it can be beneficial to periodically call the DoEvents function (e.g. once every 100 loops, or after it finishes each row/column). This will, essentially, cause Excel to check in with the Operating System and say "I'm still here, anything going on?", rather than ignoring any queries the OS might be sending it because it's too focussed on completing a task (i.e. it's not responding, which the Operating System interprets as a Crash)

how can i optimize my code vba so , it can run effectively?

1 Answers1