CODEWITHSUNDEEP
excelvbaformula
MatTtT
MatTtT

Reputation: 387

Fill formula down till last row in column

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.

Upvotes: 21

Views: 349599

Answers (4)

SteveG
SteveG

Reputation: 11

Wonderful answer! I needed to fill in the empty cells in a column where there were titles in cells that applied to the empty cells below until the next title cell.

I used your code above to develop the code that is below my example sheet here. I applied this code as a macro ctl/shft/D to rapidly run down the column copying the titles.

--- Example Spreadsheet ------------ Title1 is copied to rows 2 and 3; Title2 is copied to cells below it in rows 5 and 6. After the second run of the Macro the active cell is the Title3 cell. 

 ' **row** **Column1**        **Column2**
 '    1     Title1         Data 1 for title 1
 '    2                    Data 2 for title 1
 '    3                    Data 3 for title 1
 '    4     Title2         Data 1 for title 2
 '    5                    Data 2 for title 2
 '    6                    Data 3 for title 2
 '    7   Title 3          Data 1 for title 3

----- CopyDown code ----------

Sub CopyDown()
Dim Lastrow As String, FirstRow As String, strtCell As Range
'
' CopyDown Macro
' Copies the current cell to any empty cells below it.   
'
' Keyboard Shortcut: Ctrl+Shift+D
'
    Set strtCell = ActiveCell
    FirstRow = strtCell.Address
' Lastrow is address of the *list* of empty cells
    Lastrow = Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Address
'   MsgBox Lastrow
    Range(Lastrow).Formula = strtCell.Formula

    Range(Lastrow).End(xlDown).Select
 End Sub

Upvotes: 1

Anastasiya-Romanova 秀
Anastasiya-Romanova 秀

Reputation: 3378

Alternatively, you may use FillDown

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

Upvotes: 12

Monthy
Monthy

Reputation: 160

For people with a similar question and find this post (like I did); you can do this even without lastrow if your dataset is formatted as a table.

Range("tablename[columnname]").Formula = "=G3&"",""&L3"

Making it a true one liner. Hope it helps someone!

Upvotes: 10

Siddharth Rout
Siddharth Rout

Reputation: 149335

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

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

Upvotes: 49

Related Questions