CODEWITHSUNDEEP
excelvba
Mil13
Mil13

Reputation: 25

VBA autofill last row for two columns

I would like to autofill the formulas in columns 4 and 5, with the assumption that i do not know which row contains the last piece of data.

**realized i fudged the question, made a slight edit. TIA

https://i.sstatic.net/3m6SV.png

Upvotes: 0

Views: 93

Answers (1)

chris neilsen
chris neilsen

Reputation: 53155

Two parts to this:

  1. getting a reference to the Formulas to copy, and the area to fill
  2. copying the formulas

See inline comments

Sub Demo()
    Dim rFormulaToCopy As Range
    Dim FormulaColumn As Long, NumOfFormula As Long
    Dim DataColumn As Long
    
    '~~~ update to match your sheet layout
    FormulaColumn = 4
    NumOfFormula = 2
    DataColumn = 1
    
    With ActiveSheet '~~~ specify the required sheet
        ' Reference the last row of Formula
        Set rFormulaToCopy = .Cells(.Rows.Count, FormulaColumn).End(xlUp).Resize(, NumOfFormula)
        ' Reference the missing formula range, including the last Formula row.
        ' Assign from/to the Formula property to copy formulas (Excel will update relative references)
        rFormulaToCopy.Resize(.Cells(.Rows.Count, DataColumn).End(xlUp).Row - rFormulaToCopy.Row + 1).Formula = rFormulaToCopy.Formula
    End With
End Sub

Upvotes: 1

Related Questions