Fill formula down till last row in column

excel vba formula
MatTtT picture MatTtT · Sep 11, 2014 · Viewed 267.2k times · Source

I'm trying to draw down the formula that's in cell M3 to the end of the data set.

I'm using column L as my base to determine the last cell with data. My formula is a concatenation of two cells with a text comma in-between them.

My formula is =G3&","&L3

I want Excel to draw down this formula so

Cell M4 would be =G4&","&L4
Cell M5 would be =G5&","&L5 and so on.

My code: 

Range("$M$3").Formula = Range("G3") & (",") & Range("L3")

Dim Lastrow As Long

Application.ScreenUpdating = False

Lastrow = Range("L" & Rows.Count).End(xlUp).Row
Range("M4").FormulaR1C1 = Range("G4") & (",") & Range("L4")
Range("M4").AutoFill Destination:=Range("M4:M" & Lastrow)
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True

My output is pulling down the text values from cell M3 all the way down to the end of the data set. I've searched around for several hours trying to look for a fix, but can't find one that is trying to accomplish what I'm going for.

Answer

Siddharth Rout picture Siddharth Rout · Sep 11, 2014

It's a one liner actually. No need to use .Autofill

Range("M3:M" & LastRow).Formula = "=G3&"",""&L3"

Related questions

VBA setting the formula for a cell

I'm trying to set the formula for a cell using a (dynamically created) sheet name and a fixed cell address. I'm using the following line but can't seem to get it working: "=" & strProjectName & "!" & Cells(2, 7).Address Any advice …

Read More
How can I run VBA code each time a cell gets its value changed by a formula?

How can I run a VBA function each time a cell gets its value changed by a formula? I've managed to run code when a cell gets its value changed by the user, but it doesn't work when the value …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More