Selection.AutoFill Destination not working as desired

Question

I have following code First it will insert two columns one by one Then count rows in C column Then Copy ActiveCell.FormulaR1C1 = "=R[-5]C[18]" to B7 to rows count Similarly, Copy ActiveCell.FormulaR1C1 = "=R[-5]C[13]" to A7 to rows count But I am getting error when the Active Row is only 01 (One), if it is more than one then it works ok. I am struggling with this. If anyone can please help.

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Dim LastRow As Long
LastRow = Range("C" & Rows.Count).End(xlUp).Row

Range("B7").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-5]C[18]"
Range("B7").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False

Selection.AutoFill Destination:=Range("B7:B" & LastRow), Type:=xlFillCopy
Range("B7:B" & LastRow).Select

Range("A7").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-5]C[13]"
Range("A7").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False

Selection.AutoFill Destination:=Range("A7:A" & LastRow), Type:=xlFillCopy
Range("A7:A" & LastRow).Select

Instead of using `AutoFill`, maybe do `Range("B7:B" & LastRow).Value = Range("B7").Value`, and similarly for column A. — BigBen, Sep 14 '20 at 13:27
This has been asked so many times in the past.. [HERE](https://stackoverflow.com/questions/59945293/dynamic-autofill-destination) is one such link. — Siddharth Rout, Sep 14 '20 at 13:44

BigBen · Answer 1 · 2020-09-14T15:38:34.547

1

To close the question out:

Instead of using AutoFill, maybe do Range("B7:B" & LastRow).Value = Range("B7").Value, and similarly for column A.

Or just in one line for both columns: Range("A7:B" & LastRow).Value = Range("A7:B7").Value.

edited Sep 14 '20 at 15:38

answered Sep 14 '20 at 13:40

BigBen

46,229
7
24
40

score 0 · Answer 2 · edited Sep 14 '20 at 14:26

First off, the Selection object is not necessarily the best option. You can insert the columns with Range("A:B").Insert xlShiftToRight.

Next, you can set the formula for an entire range at once rather than copying and pasting. Since you're putting the formulas only starting at row 7, you'll need to offset and resize the range that is defined by what is in Column C (after the insert).

The Resize method will need to reduce the row count by 6 since the formulas don't start until row 7:.Resize(LastRow - 6). Since the original range is only one column wide, the column count in the Resize method can be omitted (otherwise, it'd be .Resize(LastRow - 6, 1)).

The Offset method will need to shift down 6 rows and left 1 (or 2) columns: .Offset(6,-1) and .Offset(6,-2)).

Your code would then be greatly simplified to:

Dim LastRow As Long
ActiveSheet.Range("A:B").Insert xlShiftToRight

LastRow = Range("C" & Rows.Count).End(xlUp).Row

With Range("C:C").Resize(LastRow-6) 
    .Offset(6,-1).FormulaR1C1="=R[-5]C[18]"
    .Offset(6,-2).FormulaR1C1="=R[-5]C[13]"
End With

Of course, even easier would be to convert your sheet to a Table ("ListObject" in VBA) and let Excel do the heavy lifting. But that's way outside what you asked.

I guess I overlooked that you were wanting the values, not the formulas. Sorry. — pdtcaskey, Sep 14 '20 at 14:23

Selection.AutoFill Destination not working as desired

2 Answers2