CODEWITHSUNDEEP
excelvbarangeautofill
paran
paran

Reputation: 199

How to auto fill the column of the active cell till the last row

Trying to execute below code:

ActiveCell.Offset(1, 0).Select 'Noting but F2





  ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)"





' find last row with data in column "A"

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



' copying the formula from "F2" all the way to the last cell

ActiveCell.AutoFill Destination:=Range(ActiveCell.EntireColumn & lastRow), Type:=xlFillDefault

It gives an error saying type mismatch.

But when I try:

ActiveCell.AutoFill Destination:=Range("F2:F" & lastRow), Type:=xlFillDefault

It works properly, but I don't want F2:F there, as I don't know which column will be in future.

Upvotes: 0

Views: 7342

Answers (1)

Igor
Igor

Reputation: 157

To answer your question you are not specifying a right range. A simple range can be, as you said, specified

  1. Using the string format Range("A1:B4") where A1 and B4 are two opposite corners of the range.

  2. Using the two corners as cells, e.g. Range( Cells(1,1), Cells(4,2) ) where Cells(1,1) is A1 and Cells(4,2) is B4

So you can write

ActiveCell.AutoFill Destination:=Range(Cells(2, ActiveCell.Column), Cells(lastRow, ActiveCell.Column)), Type:=xlFillDefault

To reach your goal you could also avoid using the AutoFill. FormulaR1C1 format allow you to write formula with 'relative' cell offsets/address. So after the AutoFill all the cells in the desiderd column will have the same FormulaR1C1. Then you can avoid using the AutoFill and manually set the same FormulaR1C1 for the whole column.

So you can write a simpler code like that:

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(1, ActiveCell.Column), Cells(lastRow, ActiveCell.Column)).FormulaR1C1 = "=LEFT(RC[-1],4)"

Upvotes: 1

Related Questions