Code to copy formula to last row of data

[usui]

Hi Guys,

I'm trying to do a concatenate on some data that would base on how many data present on column A (header not included) and column B1-F1 (header not included), I tried recording a macro but unfortunately even though column A is only counts until A16(not fix number and may changes depends on data needed), I needed the cells starting from B2(fix)-F1000(can be change) and then remove the formula after (paste Value only).

The issue is since range was set to B2:F1000, the concatenate did not stop on the last data on column A (A16) and continued until it reach cell 1000.

I wanted a code that if concatenate reach the last data on column A (A16-can be change) will automatically stop there instead.

Sample here: I don't want the cells on yellow

Here is the code I got after recording macro, I changed the range until F1000 since data needed may changes on column A

Sub Concat()

ActiveCell.FormulaR1C1 = "=CONCATENATE(RC1,""-"",R1C)"
Range("B2").Select
Selection.Copy
Range("B2:F1000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

I hope someone can help modify this code or if anyone who has a better code instead, this should be the formula use: =CONCATENATE($A2,"-",B$1)
Thank you in advance

 

[usui]

Try this

Option Explicit
Sub usui_V1()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<< change to suit
  
    With ws.Range("B2:F" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
        .FormulaR1C1 = "=RC1&""-""&R1C"
        .Value = .Value
    End With
End Sub

Before

usui.xlsm
	A	B	C	D	E	F
1	Square	WF-CAN-12x12	WF-CAN-20x20	WF-CAN-30x30	WF-CAN-36x36	WF-CAN-48x48
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17
Sheet1

After

usui.xlsm
	A	B	C	D	E	F
1	Square	WF-CAN-12x12	WF-CAN-20x20	WF-CAN-30x30	WF-CAN-36x36	WF-CAN-48x48
2	1	1 - WF-CAN-12x12	1 - WF-CAN-20x20	1 - WF-CAN-30x30	1 - WF-CAN-36x36	1 - WF-CAN-48x48
3	2	2 - WF-CAN-12x12	2 - WF-CAN-20x20	2 - WF-CAN-30x30	2 - WF-CAN-36x36	2 - WF-CAN-48x48
4	3	3 - WF-CAN-12x12	3 - WF-CAN-20x20	3 - WF-CAN-30x30	3 - WF-CAN-36x36	3 - WF-CAN-48x48
5	4	4 - WF-CAN-12x12	4 - WF-CAN-20x20	4 - WF-CAN-30x30	4 - WF-CAN-36x36	4 - WF-CAN-48x48
6	5	5 - WF-CAN-12x12	5 - WF-CAN-20x20	5 - WF-CAN-30x30	5 - WF-CAN-36x36	5 - WF-CAN-48x48
7	6	6 - WF-CAN-12x12	6 - WF-CAN-20x20	6 - WF-CAN-30x30	6 - WF-CAN-36x36	6 - WF-CAN-48x48
8	7	7 - WF-CAN-12x12	7 - WF-CAN-20x20	7 - WF-CAN-30x30	7 - WF-CAN-36x36	7 - WF-CAN-48x48
9	8	8 - WF-CAN-12x12	8 - WF-CAN-20x20	8 - WF-CAN-30x30	8 - WF-CAN-36x36	8 - WF-CAN-48x48
10	9	9 - WF-CAN-12x12	9 - WF-CAN-20x20	9 - WF-CAN-30x30	9 - WF-CAN-36x36	9 - WF-CAN-48x48
11	10	10 - WF-CAN-12x12	10 - WF-CAN-20x20	10 - WF-CAN-30x30	10 - WF-CAN-36x36	10 - WF-CAN-48x48
12	11	11 - WF-CAN-12x12	11 - WF-CAN-20x20	11 - WF-CAN-30x30	11 - WF-CAN-36x36	11 - WF-CAN-48x48
13	12	12 - WF-CAN-12x12	12 - WF-CAN-20x20	12 - WF-CAN-30x30	12 - WF-CAN-36x36	12 - WF-CAN-48x48
14	13	13 - WF-CAN-12x12	13 - WF-CAN-20x20	13 - WF-CAN-30x30	13 - WF-CAN-36x36	13 - WF-CAN-48x48
15	14	14 - WF-CAN-12x12	14 - WF-CAN-20x20	14 - WF-CAN-30x30	14 - WF-CAN-36x36	14 - WF-CAN-48x48
16	15	15 - WF-CAN-12x12	15 - WF-CAN-20x20	15 - WF-CAN-30x30	15 - WF-CAN-36x36	15 - WF-CAN-48x48
17
Sheet1

error sir

 

[kevin9999]

View attachment 75030error sir

Which line was highlighted?

 

[mohadin]

You are very welcome
And thank you for the feedback
BE happy and safe

 

[usui]

Try

Sub Concat()
    With Range("B2:F" & Cells(Rows.Count, 1).End(xlUp).Row)
    .Select
        .FormulaR1C1 = "=CONCATENATE(RC1,""-"",R1C)"
        .Value = .Value
    End With
End Sub

Works great sir, thank you so much for this.

 

[Jimmypop]

Hi Sir, it works but the issue is that I need to RUN the macro twice in order for it to work..1st RUN it just shows highlighted cells then if I RUN it again (2nd Run) then everything shows up and good.

Is this normal that I need to RUN it twice before the data appears?

1st Run:
View attachment 75027
2nd Run:
View attachment 75028

Hi.. not sure why but I see you did get sorted with a much shorter code... have a good day further