Perfomance of VBA query

Resolved · Medium Priority · Version 2010

Tue 1 Sep 2015, 14:00 Delegate Antonio said...

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

Tue 1 Sep 2015, 14:29 Trainer Jens said...

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Random Numbers

Type =RAND()*200 to generate a number between 1 and 200.
Use the fill handle to drag down and populate as many cells as you'd like with random numbers.

View all Excel hints and tips