patternMinor
Trying to make more simple VBA
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?
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 SubSolution
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.