CODEWITHSUNDEEP
excelvba
Rilude
Rilude

Reputation: 11

Excel VBA autofill based on dynamic range

Please can you help with the below; I have an increasing list of staff listed in A1, and I want my formulas in E1, F1, G1, etc. to be autofilled and not static like I have now.

I have the below but its throwing up the error: Method 'Range' of object '_Global' failed

My vba code;

Sub AutoFill()

    Dim RowCount As Variant

    RowCount = Range(("A1"), Range("A1").End(xlDown)).Rows.Count
    Range("E1").Select ' Default formula's in E1
    Selection.AutoFill Destination:=Range(RowCount - 1), Type:=xlFillDefault

End Sub

What am I missing, i'm stumped and have been since yesterday...

Thanks in Advance.

Upvotes: 1

Views: 15657

Answers (1)

user10781941
user10781941

Reputation:

The Range.FillDown method method may be easier to code.

Sub DynAutoFill()

    Dim lastRow As long

    lastRow = cells(rows.count, "A").end(xlup).row  'should -1 be added here?
    Range(cells(1, "E"), cells(lastRow, "E")).filldown

End Sub

FillDown can easily handle multiple columns.

Sub DynAutoFill()

    Dim lastRow As long

    lastRow = cells(rows.count, "A").end(xlup).row  'should -1 be added here?
    Range(cells(1, "E"), cells(lastRow, "G")).filldown

End Sub

Your own code needs the entire range as the destination.

Sub DynAutoFill()

    Dim RowCount As Variant

    RowCount = Range(("A1"), Range("A1").End(xlDown)).Rows.Count
    Range("E1").AutoFill Destination:=Range(cells(1, "E", cells(RowCount - 1, "E")), Type:=xlFillDefault

End Sub

I wouldn't use a reserved word for the name of the sub procedure.

Upvotes: 2

Related Questions