HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Trying to make more simple VBA

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
simpletryingmoremakevba

Problem

I have few models like this below for diferent tools i manage. I am trying to clean/improve the code... Could you help me?

All the informations comes inside columns B to N, and the column P concatenate it with ";". There are 6 sheets i do the same thing.

Any ideas to have a better performance and cleaner code?

Private Sub bov_mobile()
Sheets("Mobile").Select
Columns("p").Clear
fim = Cells(Rows.count, 1).End(xlUp).Row

For i = 2 To fim
Range("A" & i).Select

    If ActiveCell.Offset(0, 14).Value = "BOV" Or ActiveCell.Offset(0, 14).Value = "BOV BMF" Then
        Range("P" & i).FormulaR1C1 = "=RC[-14]&"";""&RC[-13]&"";""&RC[-12]&"";""&RC[-11]&"";""&RC[-10]&"";""&RC[-9]&"";""&RC[-8]&"";""&RC[-7]&"";""&RC[-6]&"";""&RC[-5]&"";""&RC[-4]&"";""&RC[-3]&"";""&RC[-2]&"";"""
        Range("P" & i).Copy
        Selection.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End If
Next i

    ' ###############  ORDER BY
    Range("p2", Cells(Rows.count, 16).End(xlUp)).Select
    Selection.Sort Key1:=Range("P2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

Range("P2", Cells(Rows.count, 16).End(xlUp)).Copy

Sheets("Mod_Bov").Select
Cells(Rows.count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

End Sub

Solution

The quickest fix you can make to improve speed is removing all of the Select statements, and instead just referencing those selected ranges with variables. (Select slows down any VBA code significantly). You can also toggle Application.ScreenUpdating to False before the code and True after to gain run-time speed.

Context

StackExchange Code Review Q#134757, answer score: 5

Revisions (0)

No revisions yet.