Fill in the entire column according to the last data in the table - Does not work

Question

I have a formula in Column A2. I have a table similar to this:

Formula	Note	Datum	I am very happy because I am	Years	years old
=CONCATENATE(TEXT(C2;"dd-mm-yyyy");$D$1;E2;$F$1)	Any word, TEXT	01.04.2021		21
Autofill	Any word, TEXT 2	01.04.2021		25

I want to transfer it and use it automatically for the whole column. However, I tried possible and impossible ways to do it, but none of them worked. I also looked at forums such as here:

I don't have all the data filled in the table, so I want "excel" to look for the last row in which the record is and try to calculate the formula and return it to the last cell in column A.

Thank you in advance for all the help

(The formula joins the text together) =CONCATENATE(TEXT(C2;"dd-mm-yyyy");$D$1;E2;$F$1)

Sub AutofilCol()
'  Apply to the entire column Autofill
        Range("A1").Offset(1, 0).Activate
        ActiveCell.FormulaR1C1 = _
"=CONCATENATE(TEXT(RC[2],""dd-mm-yyyy""),R1C4,RC[4],R1C6)"
'  AutoFill    
     Selection.AutoFill Destination:=Range("A2:A").End(xlDown).Row
     ActiveCell.EntireColumn.AutoFit
End Sub 

Answer 1

Copy Formulas (Defining a Range)

• In this case, there is no need to Activate (or Select) anything neither is the use of AutoFill (FillDown).
• Let's say the first solution is the most flexible (reliable) but also the most complex. To better understand it, see the ranges at the various stages of the code printed in the Immediate window (CTRL+G). The flexibility is in the option to use any first cell address e.g. C5, D10, etc. and it will still work.
• Depending on your data, you might easily get away with the remaining two solutions.
• I didn't include any solution using End since you got that covered by another post.

Option Explicit

Sub copyFormulas()
    
    Const First As String = "A1"
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    Dim fCell As Range ' Last Cell in First Row Range
    Dim frg As Range ' First Row Range of Table Range
    With ws.Range(First)
        Set fCell = .Resize(, .Worksheet.Columns.Count - .Column + 1) _
            .Find("*", , xlFormulas, , , xlPrevious)
        If fCell Is Nothing Then Exit Sub
        Set frg = .Resize(, fCell.Column - .Column + 1)
        Debug.Print "First", fCell.Address, frg.Address
    End With
    
    Dim tCell As Range ' Last Cell in Table Range
    Dim trg As Range ' Table Range
    With frg
        Set tCell = .Resize(.Worksheet.Rows.Count - .Row + 1) _
            .Find("*", , xlFormulas, , xlByRows, xlPrevious)
        Set trg = .Resize(tCell.Row - .Row + 1)
    End With
    Debug.Print "Table", tCell.Address, trg.Address
    
    Dim drg As Range ' Destination Range
    Set drg = trg.Columns(1).Resize(trg.Rows.Count - 1).Offset(1)
    Debug.Print "Destination", drg.Address
    
    drg.FormulaR1C1 = "=CONCATENATE(TEXT(RC[2],""dd-mm-yyyy""),R1C4,RC[4],R1C6)"
    ' Or.
    'drg.Formula = "=CONCATENATE(TEXT(C2,""dd-mm-yyyy""),$D$1,E2,$F$1)"
    
End Sub


Sub copyFormulasUsedRange()
    With ActiveSheet.UsedRange.Columns(1)
        .Resize(.Rows.Count - 1).Offset(1).FormulaR1C1 _
            = "=CONCATENATE(TEXT(RC[2],""dd-mm-yyyy""),R1C4,RC[4],R1C6)"
    End With
End Sub


Sub copyFormulasCurrentRegion()
    With ActiveSheet.Range("A1").CurrentRegion.Columns(1)
        .Resize(.Rows.Count - 1).Offset(1).FormulaR1C1 _
            = "=CONCATENATE(TEXT(RC[2],""dd-mm-yyyy""),R1C4,RC[4],R1C6)"
    End With
End Sub

Answer 2

It looks like this is what you want to do:-

Sub AutofillCol()

    Dim Rl      As Long         ' last used row in column C
    Dim Rng     As Range
    
    Rl = Cells(Rows.Count, "C").End(xlUp).Row
    Set Rng = Range(Cells(2, "A"), Cells(Rl, "A"))
    Rng.FormulaR1C1 = "=CONCATENATE(TEXT(RC[2],""dd-mm-yyyy""),R1C4,RC[4],R1C6)"
End Sub