VBA code to Autofill one cell to many rows below where row count will change

[TrainingGoddess]

Sub AutoFillDateLookups()
Dim DataRange As Range
Dim CopyRange As Range
Dim FillRange As Range
Set DataRange = Range("ClassCountRevenue")
Set CopyRange = Range("F2")
Set FillRange = CopyRange.Offset(1, 0).Resize(DataRange.Rows.Count,
0).Select
CopyRange.AutoFill Destination:=Range(FillRange)
End Sub

I am attempting to AutoFill a range of values from one cell (F2) to a
range of cells below where the number of rows will vary from month to
month.

In the example above, I have set the datarange to capture the total
rows/columns in the data table. I set the copy range to the starting
cell containing my formula. I can't seem to get the fill range to be
one row down and then resize to the total count of rows in the data
table.

Any help would be much appreciated.

Thanks!
Kimberly

 

[Guest]

Kimberley

I approach this slightly differently. First find you last used row and then
insert the required formula into all cells in the column base on this last
row. This means you do not need to use the Autofill. e.g.

Dim endRow As Long
endRow = Range("E" & Rows.Count).End(xlUp).Row
Range(Cells(2, 6), Cells(endRow, 6)).FormulaR1C1 _
= "=RC[-2]+RC[-3]"

Hope this helps
Rowan

 

[Guest]

Otherwise if the formula is not coming from your VBA code to start with you
can use yuor macro as follows:

Sub AutoFillDateLookups()
Dim DataRange As Range
Dim CopyRange As Range
Dim FillRange As Range
Set DataRange = Range("ClassCountRevenue")
Set CopyRange = Range("F2")
CopyRange.AutoFill Destination:=Range(Cells(2, 6), _
Cells(Range("E" & Rows.Count).End(xlUp).Row, 6))

End Sub

Regards
Rowan

 

[Jim Cone]

Kimberly,

Several things ...
Resize: you must omit or specify the number of columns, 0 is not valid.
AutoFill: the destination range must include the source range.
FillRange: It is already a range object, so Range(FillRange) is invalid.

The following will work if DataRange is a valid range object...
'----------------------------------------
Sub AutoFillDateLookups()
Dim DataRange As Range
Dim CopyRange As Range
Dim FillRange As Range

Set DataRange = Range("ClassCountRevenue")
Set CopyRange = Range("F2")
Set FillRange = CopyRange.Resize(DataRange.Rows.Count + 1, 1)

CopyRange.AutoFill Destination:=FillRange
End Sub
'-------------------------------------------

Regards,
Jim Cone
San Francisco, USA

 

[Kimberly Mills]

Thanks for your help!

 

[Kimberly Mills]

Thanks for your help!