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

HayabusaPR

New Member
Joined
Jul 16, 2019
Messages
14
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


Code:
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
 
Last edited by a moderator:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
so what you want is to put a variable for last row at the beginning of your code

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

Code:
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
Code:
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
 
Last edited:

HayabusaPR

New Member
Joined
Jul 16, 2019
Messages
14
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

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows

No problem!
Thanks for the feedback.

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

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

to

Code:
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
 

Forum statistics

Threads
1,148,282
Messages
5,745,836
Members
423,981
Latest member
ph1l

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