Can you speed up this code?

C

CLR

Hi All......
I have a user who complains that his following code runs too slow and is
wondering how it might be made faster........can anyone help please?

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("Data Entry").Select
Range("J1").Select
x = 3
Dim y As Integer
Do While Selection.Offset(x, 0) <> ""
y = Selection.Offset(x, 0).Value
For Z = 4 To 15
Selection.Offset(x, Z).Value = Worksheets("Shop
Input").Cells(y, Z).Value
Next Z
x = x + 1
Loop

Range("A1").Select
Application.ScreenUpdating = True
End Sub

TIA
Vaya con Dios,
Chuck, CABGx3
 
R

RB Smissaert

Assign the relevant ranges to array like this:

Dim arr() 'this can be a lot faster than doing Dim arr

arr = Range(Cells(1), Cells(100, 5))

Then do your alterations in the array(s)

Then write the array back to a sheet range like this:

Range(Cells(1), Cells(100, 5)) = arr

Will be a lot faster.


RBS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Looping(Rookies) 4
How to speed up code 5
VBA speed up 0
Help Reviewing My Loop 1
Speed up routine 5
Help with Excel VBA QUOTIENT 5
Chart disappearing in Excel 07 but not in 03 2
What is wrong with this code?!? 1

Top