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

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




"CLR" <(E-Mail Removed)> wrote in message
news:7895F45B-F8FD-4F28-959B-(E-Mail Removed)...
> 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
>

Many thanks RB, will give it a try.......

Vaya con Dios,
Chuck, CABGx3


"RB Smissaert" wrote:

> 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
>
>
>
>
> "CLR" <(E-Mail Removed)> wrote in message
> news:7895F45B-F8FD-4F28-959B-(E-Mail Removed)...
> > 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
> >
>
> .
>