Fill in the entire column according to the last data in the table

Slavio

New Member
Joined
Mar 28, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
  2. Web
I have a table similar to this:
I have a formula in Column A2.

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: vba to drag down formula/data from last filled row to next row etc.
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.

(The formula joins the text together)

FormulaNoteDatumI am very happy because I amYearsyears old
=CONCATENATE(TEXT($C$2;"dd-mm-yyyy");$D$1;E2;$F$1)Any word, TEXT01.04.202121

There is an error in the last two lines.
The formula is applied to cell A2 only. Does not continue downwards.

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

Thanks
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Slavio

New Member
Joined
Mar 28, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
  2. Web
I have a table similar to this:
I have a formula in Column A2.

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: vba to drag down formula/data from last filled row to next row etc.
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.

(The formula joins the text together)

FormulaNoteDatumI am very happy because I amYearsyears old
=CONCATENATE(TEXT($C$2;"dd-mm-yyyy");$D$1;E2;$F$1)Any word, TEXT01.04.202121

There is an error in the last two lines.
The formula is applied to cell A2 only. Does not continue downwards.

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

Thanks
Current formula=CONCATENATE(TEXT(C2;"dd-mm-yyyy");$D$1;E2;$F$1)
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(TEXT(RC[2],""dd-mm-yyyy""),R1C4,RC[4],R1C6)"
 

Slavio

New Member
Joined
Mar 28, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Do I have to find the answer alone? :)
This solution for filling the column to the last row works. However, data must also be in a different column.

lastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("A2").AutoFill Destination:=Range("A2:A" & lastRow)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,187
Messages
5,640,693
Members
417,161
Latest member
Devon150

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top