Antonio has attended:
Excel VBA Introduction course
Perfomance of VBA query
Hi, I have created a macro to perform a task but it is slow. As I am new to VBA was hoping there are some tips to speed up my coding
Sub Wes()
For G = 1 To Sheets("sheet2").Range("a1").CurrentRegion.Rows.Count
Sheets("sheet2").Select
Cells(G + 1, 5).Select
For x = 1 To 10
Sheets("sheet2").Select
If Cells(G + 1, 5).Offset(0, x - 1).Value = "X" Then
Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 3)).Copy
Sheets("Sheet1").Select
Range("e1:g3").Offset((Range("a1").CurrentRegion.Rows.Count), 0).Select
ActiveSheet.Paste
Sheets("sheet2").Select
Range("E2").Offset(-1, x - 1).Copy
Sheets("Sheet1").Select
Range("I1:I3").Offset((Range("a1").CurrentRegion.Rows.Count) - 3, 0).Select
ActiveSheet.Paste
Range("o1").Offset((Range("a1").CurrentRegion.Rows.Count) - 3, 0).Value = "20"
Range("o1").Offset((Range("a1").CurrentRegion.Rows.Count) - 2, 0).Value = "40"
Range("o1").Offset((Range("a1").CurrentRegion.Rows.Count) - 1, 0).Value = "40H"
Sheets("sheet2").Select
Range(Cells(ActiveCell.Row, 16), Cells(ActiveCell.Row, 18)).Copy
Sheets("Sheet1").Select
Range("q1").Offset((Range("a1").CurrentRegion.Rows.Count) - 3, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Transpose:=True
Sheets("sheet2").Select
Cells(ActiveCell.Row, 15).Copy
Sheets("Sheet1").Select
Range("m1:m3").Offset((Range("a1").CurrentRegion.Rows.Count) - 3, 0).Select
ActiveSheet.Paste
End If
Next x
Next G
End Sub
RE: Perfomance of VBA query
Hi Antonio,
Yes there are many things to do to speed up the code
first just under the line Sub Wes() type:
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
and just before the line End Sub type:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Our Excel Intermediate course would be useful for you because you can speed up the code a lot using variables.
instead of the line:
For G = 1 To Sheets("sheet2").Range("a1").CurrentRegion.Rows.Count
you should have:
Dim lCounter as long lCounter=Sheets("sheet2").Range("a1").CurrentRegion.Rows.Count
For G=1 to lCounter
If you change this Excel will not have to count the number of rows each time the loop is running.
You have the same issue many time in the code where you count number of rows in the currentregion. You should create variables and store the number of rows in variables.
I hope this can help you in the right direction.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
