Updating arrays in a macro

[hobee11]

Hi

I run a large macro every day and have found my arrays to not always recalculate, so I have written this sub macro below. It is working fine but one problem. This line statement :

Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown)), Type:=xlFillDefault


I want it actually to autofill down 200 rows instead of "selection.End(xlDown)"

Pls help. Ty


------------------------------------------------------------------------

Sub Update_value_arrays()

Dim Row As Integer

Sheets("Sheet1").Select

Range("Y5").Select ' Y5 value contains the # of the last row

Row = ActiveCell.Value

Range("DP1").Offset(Row - 100, 0).Select ' The 1st column of the target array is in DP column

Range(Selection, ActiveCell.End(xlToRight)).Select ' Row selection of the individual arrays

Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown)), Type:=xlFillDefault ' Re-fill local arrays

ActiveSheet.Calculate


End Sub

 

[Hui]

Hobee11


Firstly, Welcome to the Chandoo.org Forums


A couple of things here:


You can set the Row value without selecting it, this is much faster

[pre]

'Range("Y5").Select
Row = Range("Y5").Value[/pre]
I think for your range you want:

Selection.AutoFill Destination:=Selection.Resize(Row), Type:=xlFillDefault ' Re-fill local arrays

 

[hobee11]

That works, Hui. Thanks. I understand it.