Dynamic Autofill Destination

Question

Need some help with this code:

Dim numRows As Long

numRows = Cells(Rows.Count, "A").End(xlUp).Row

Selection.AutoFill Destination:=Range(Cells(1, 12), Cells(numRows, 12)), Type:=xlFillDefault

The macro is supposed to fill column L with a formula. It works with files with multiple rows but ends with:

Autofill method of Range class failed.

For files with only one row.

It will fail the autofill if there is nothing to autofill. This is expected behaviour, and this might be one of the few valid cases you could use `On Error Resume Next` to get around it. Alternatively, you could use an if statement to test if `numRows > 1`, and skip the autofill when true. — Plutian, Jan 28 '20 at 09:12
@Plutian Don't use `On Error Resume Next` this is a very bad practice! And it will soon or later result in other issues. Instead check for `If numRows > 1 Then 'do autofill`. You might benefit from reading [VBA Error Handling – A Complete Guide](https://excelmacromastery.com/vba-error-handling) — Pᴇʜ, Jan 28 '20 at 09:15
You can use `.Formula` to enter the values in the entire range in one go... `ws.Range("L1:L" & numRows).Formula = ws.Range("L1").Formula` Where `ws` is the respective sheet. BTW your cells are not fully qualified. You may want to see [THIS](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) — Siddharth Rout, Jan 28 '20 at 09:21
@Pᴇʜ thanks for the share, definitely a good read. I realised my advise was bad as soon as I started typing up the answer, so I amended with the preferred method and a disclaimer. — Plutian, Jan 28 '20 at 09:22

score 3 · Answer 1 · answered Jan 28 '20 at 09:26

Tips:

Avoid the use of Selection. In your code, the selection may not be the "selection" you think it is ;) You may want to see How to avoid using Select in Excel VBA
Your objects are not fully qualified. You may want to see THIS
You can use .Formula to enter the values in the entire range in one go.

Is this what you are trying (Untested)?

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long

    Set ws = Sheet1 '<~~ Change as applicable

    With ws
        '~~> Get the last row
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("L1:L" & lRow).Formula = .Range("L1").Formula
    End With
End Sub

I expected when pasting the same formula to an entire range as in your answer, it would copy the same formula to all cells, instead of auto-incrementing. But after testing found it doesn't work like that. Interesting... — Plutian, Jan 28 '20 at 09:37

Plutian · Answer 2 · 2020-01-28T09:31:48.243

2

Test if the row count is equal to or lower than zero with an if statement:

Dim numRows As Long

With Sheets("Yoursheetname")

    numRows = .Cells(.Rows.Count, "A").End(xlUp).Row

    If Not numRows <= 1 Then .Cells(1, 12).AutoFill Destination:=.Range(.Cells(1, 12), .Cells(numRows, 12)), Type:=xlFillDefault

End With

As per my comment, you could also use On Error Resume Next:

Dim numRows As Long

numRows = Cells(Rows.Count, "A").End(xlUp).Row

On Error Resume Next 'turn of error handling for the next statement

Selection.AutoFill Destination:=Range(Cells(1, 12), Cells(numRows, 12)), Type:=xlFillDefault

On Error GoTo 0 'turn error handling back on

Edit: as per @Peh's comment, the first one is preferred, as turning off error handling (even for one statement) might become problematic if you code has (or develops) any other issues.

edited Jan 28 '20 at 09:31

answered Jan 28 '20 at 09:18

Plutian

2,276
3
14
23

1

Avoid the use of selection :) – Siddharth Rout Jan 28 '20 at 09:22
@SiddharthRout Edited without the use of selection, used explicit sheet references throughout the code as well. [Obligatory link](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Plutian Jan 28 '20 at 09:32
2

++ nice.. btw `If Not numRows <= 1` can be written as `If numRows > 1` :) – Siddharth Rout Jan 28 '20 at 09:36
This is what overthinking looks like :P – Plutian Jan 28 '20 at 09:38

score 1 · Answer 3 · answered Jan 28 '20 at 10:38

1

Alternatively:

Sub Test()

Dim lr As Long

With Sheet1
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    If lr > 1 Then .Range("L1:L" & lr).FillDown
End With

End Sub

answered Jan 28 '20 at 10:38

JvdV

70,606
8
39
70

Dynamic Autofill Destination

3 Answers3

Linked