I want to paste a concatenate formula until the cell in the right is empty

[HayabusaPR]

I was able to create this Macro with the help of @Logit

in this section
' Create the Concatenate Formula and Autofill Formula to the Last Row with Data the concatenate works but it goes down to row 10,000 and I would like the macro to stop the concatenate formula when the cell on the right is empty. Thanks for the help

Sub OpenFile()
 ' Turns Off Screen Updating While the Macro is Running
    Application.ScreenUpdating = False
    
 ' Open File from Location, Copy/Paste Data and Insert the Path Column
    Workbooks.Open Sheet1.Range("B7").Value & Sheet1.Range("B6").Value
    Columns("A:R").Select
    Selection.copy
    Windows("Mapping Table Macro Working File.xlsm").Activate
    Sheets("Mapping Table").Select
    ActiveSheet.Paste
    Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Path"
    
[B]  ' Create the Concatenate Formula and Autofill Formula to the Last Row with Data
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
    "=CONCATENATE(RC[1],"" / "",RC[2],"" / "",RC[3],"" / "",RC[4],"" / "",RC[5],"" / "",RC[6],"" / "",RC[7],"" / "",RC[8],"" / "",RC[9],"" / "",RC[10],"" / "",RC[11],"" / "",RC[12],"" / "",RC[13],"" / "",RC[14],"" / "",RC[15])"
    Selection.AutoFill Destination:=Range("D2:D10000")
    Range("D2:D10000").Select[/B]
    
  ' Assing Columns Width size
    Selection.columnwidth = 11
    Columns("B:B").Select
    Selection.columnwidth = 16
    Columns("C:C").Select
    Selection.columnwidth = 11
    Columns("D:D").Select
    Selection.columnwidth = 37
    Columns("E:E").Select
    Selection.columnwidth = 18
    Columns("F:F").Select
    Selection.columnwidth = 22
    Columns("G:G").Select
    Selection.columnwidth = 11
    Columns("H:H").Select
    Selection.columnwidth = 18
    Columns("I:I").Select
    Selection.columnwidth = 19
    Columns("J:J").Select
    Selection.columnwidth = 22
    Columns("K:K").Select
    Selection.columnwidth = 13
    Columns("L:L").Select
    Selection.columnwidth = 18
    Columns("M:M").Select
    Selection.columnwidth = 18
    Columns("N:N").Select
    Selection.columnwidth = 18
    Columns("O:O").Select
    Selection.columnwidth = 18
    Columns("P:P").Select
    Selection.columnwidth = 18
    Columns("Q:Q").Select
    Selection.columnwidth = 18
    Columns("R:R").Select
    Selection.columnwidth = 18
    Columns("S:S").Select
    Selection.columnwidth = 18
    
  ' Format File
    Cells.Select
    With Selection.Font
        .Name = "Arial"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Rows("1:1").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("D:D").Select
    With Selection
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1").Select
    
' Turns screen updating back on
    Application.ScreenUpdating = True
'Displays message to user once the macro is complete running
    MsgBox "Process Completed. Have a nice day!"
End Sub

 

[DataBlake]

so what you want is to put a variable for last row at the beginning of your code

Dim lastRow As Long

Then define the variable after you've opened your file. So below the
' Open File from Location, Copy/Paste Data and Insert the Path Column
cluster you will put

lastRow = Range("A" & Rows.Count).End(xlUp).Row

replace "a" with the column you want the last row from

Then your bolded area should come out to be

Range("D2").FormulaR1C1 = _
"=CONCATENATE(RC[1],"" / "",RC[2],"" / "",RC[3],"" / "",RC[4],"" / "",RC[5],"" / "",RC[6],"" / "",RC[7],"" / "",RC[8],"" / "",RC[9],"" / "",RC[10],"" / "",RC[11],"" / "",RC[12],"" / "",RC[13],"" / "",RC[14],"" / "",RC[15])"
Range("D2").AutoFill Destination:=Range("D2:D" & lastRow)

if you're trying to do an array formula with CONCATENATE then you will swap "FormulaR1C1" with regular references and
<code>.FormulaArray</code>
i don't have the time to fully test this out, but let me know if that produces the desired result

 

[HayabusaPR]

Thank you @BlakeSkate

Your code works perfectly.

This is how the code looks:

' Create the Concatenate Formula and Autofill Formula to the Last Row with Data

Dim lastRow As Long
lastRow = Range("E" & Rows.Count).End(xlUp).Row
Range("D2").FormulaR1C1 = _
"=CONCATENATE(RC[1],"" / "",RC[2],"" / "",RC[3],"" / "",RC[4],"" / "",RC[5],"" / "",RC[6],"" / "",RC[7],"" / "",RC[8],"" / "",RC[9],"" / "",RC[10],"" / "",RC[11],"" / "",RC[12],"" / "",RC[13],"" / "",RC[14],"" / "",RC[15])"
Range("D2").AutoFill Destination:=Range("D2:D" & lastRow)

 

[DataBlake]

Thank you @BlakeSkate

No problem!
Thanks for the feedback.

I would also advise speeding up your code by eliminating selections
you can change things like

    Columns("B:B").Select
    Selection.columnwidth = 16
    Columns("C:C").Select
    Selection.columnwidth = 11
    Columns("D:D").Select
    Selection.columnwidth = 37

to

With <code class="lang-vb x-hidden-focus" data-author-content="Sheets("Sheet1").Activate
">Sheets("Sheet1")
.</code>Columns("B:B").columnwidth = 16
<code class="lang-vb x-hidden-focus" data-author-content="Sheets("Sheet1").Activate
">.</code>Columns("C:C").columnwidth = 11
<code class="lang-vb x-hidden-focus" data-author-content="Sheets("Sheet1").Activate
">.</code>Columns("D:D").columnwidth = 37
End With

just change "Sheet1" to your sheet name