98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Perfomance of VBA query
Perfomance of VBA query
Resolved · Medium Priority · Version 2010
Tue 1 Sep 2015, 14:00Delegate 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:29Trainer 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 information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Random NumbersType =RAND()*200 to generate a number between 1 and 200. |
