Using VBA to concatenate two columns

[Belinda]

Hello,

I have an Excel table that has a fixed number of columns but the number of rows can vary depending on the data update. The columns have a header row.

I want to use VBA code to concatenate the text contents of Column F and H with an underscore in between and place the contents in Column J.

E.g. Cell F2 has "FC TAB" and Column H2 has "Allegra Plus". The result in Cell J2 would be "FC TAB_Allegra Plus"

Thank you.

 

[Mike Blackman]

Hi,

One way;

Sub Concat()

Dim lRow As Long

lRow = Range("F" & Rows.count).End(xlUp).Row

For i = 2 To lRow
    Cells(i, 10) = Cells(i, 6) & "_" & Cells(i, 8)
Next i

End Sub

Although this can be done with formula;

=F2&"_"&H2 in J2 copied down.

 

[dyaron]

Sub concatMyData()

For i = 2 To Cells(Rows.Count, "F").End(xlUp).Row
    Cells(i, "J").Value = Cells(i, "F").Value & "_" & Cells(i, "H").Value
Next i

End Sub

 

[Trebor76]

Hi Belinda,

Run the following code while on the tab in question:

Sub Macro1()

Dim lngLastRow As Long
'Uses Column A to set the 'lngLastRow' variable _
(find the last row) - change if required.
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

    Range("J2").Formula = "=F2 & ""_"" & H2"
    Range("J2").Copy Range("J3:J" & lngLastRow)

End Sub

HTH

Robert

 

[Mike Blackman]

Hi Trebor,

This could be shortened as such;

Sub Macro1()

Dim lngLastRow As Long
'Uses Column A to set the 'lngLastRow' variable _
(find the last row) - change if required.
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

    Range("J2:J" & lngLastRow).Formula = "=F2 & ""_"" & H2"

End Sub

 

[Trebor76]

Hi Mikey B,

Thanks for the tip - it's really appreciated

Kind regards,

Robert

 

[mikerickson]

If you want values in the cells, Evaluate(array formula) can be used in place of copy/paste or range.Value = range.Value.

Sub Macro1()

Dim lngLastRow As Long
'Uses Column A to set the 'lngLastRow' variable _
(find the last row) - change if required.
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
   
    Range("J2:J" & lngLastRow).Value = Evaluate("=F2:F" & lngLastRow & "&""_""&" & "H2:H" & lngLastRow)

End Sub

 

[Peter_SSs]

I was working on this earlier today but got called away. Now that I have returned I see that my suggestion is very similar to Mike's, but decided I would post it anyway. I have used column F to determine the range (rather than column A) and a slightly different construct in the 'Evaluate' section.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ConCat()<br>    <SPAN style="color:#00007F">With</SPAN> Range("F2", Range("F" & Rows.Count).End(xlUp))<br>        .Offset(, 4).Value = Evaluate(.Address & "& ""_"" & " & .Offset(, 2).Address)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Belinda]

Thank you all for your help. The different approaches have been useful in making me understand VBA a little better.

 

[branedead]

I was working on this earlier today but got called away. Now that I have returned I see that my suggestion is very similar to Mike's, but decided I would post it anyway. I have used column F to determine the range (rather than column A) and a slightly different construct in the 'Evaluate' section.


Sub ConCat()
****With Range("F2", Range("F" & Rows.Count).End(xlUp))
********.Offset(, 4).Value = Evaluate(.Address & "& ""_"" & " & .Offset(, 2).Address)
****End With
End Sub

What if column F is blank? How can this be modified to not concatenate anything if column F contains a blank cell?

Thanks!