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.

 

[Peter_SSs]

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

Thanks!

Try changing the 'Evaluate' line to

.Offset(, 4).Value = Evaluate("if(" & .Address & "="""",""""," & .Address & "& ""_"" & " & .Offset(, 2).Address & ")")

 

[pawcoyote]

Hi, I am trying to use what you stated.. But modified it to the cells I would like. For some reason it runs for Rows 7 thru 11 but nothing after Row 11.

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("CE7:CE" & lngLastRow).Value = Evaluate("=Y7:Y" & lngLastRow & "&""--""&" & "X7:X" & lngLastRow & "&""--""&" & "W7:W" & lngLastRow & "&""--""&" & "AB7:AB" & lngLastRow & "&""--""&" & "AC7:AC" & lngLastRow)


End Sub

 

[Trebor76]

The lngLastRow variable is looking at column A to find the last row which it has determined to be 11. As my original said, change the code to point to another column if required or you can use the following (less efficient) to find the last row regardless of what column it resides in (as long as there's data on the tab or else it will error out):

lngLastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

Robert

 

[pawcoyote]

The lngLastRow variable is looking at column A to find the last row which it has determined to be 11. As my original said, change the code to point to another column if required or you can use the following (less efficient) to find the last row regardless of what column it resides in (as long as there's data on the tab or else it will error out):

lngLastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

Robert

Thank you, I missed adding in info on Column A... That was a Reference point for me... DOH...

Thank you!

 

[pawcoyote]

Try changing the 'Evaluate' line to

.Offset(, 4).Value = Evaluate("if(" & .Address & "="""",""""," & .Address & "& ""_"" & " & .Offset(, 2).Address & ")")

Hi, I am trying to understand how I could change my code to do nothing if the the fields are blank.. I want to include cells Y, X, W, AB and AC but only if there is data in them.

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("CE7:CE" & lngLastRow).Value = Evaluate("=Y7:Y" & lngLastRow & "&""--""&" & "X7:X" & lngLastRow & "&""--""&" & "W7:W" & lngLastRow & "&""--""&" & "AB7:AB" & lngLastRow & "&""--""&" & "AC7:AC" & lngLastRow)


End Sub

 

[Capell]

I've followed the above discussion, especially the points making use of the Evaluate function. But I'm still stuck with a problem that involves joining two column ranges (B and C) which hold date and time respectively. The old problem of joining Date and Time applies, but I can overcome this with any of the code suggestions above.

(How on earth do you enter code here?)

 

[mikerickson]

...
(How on earth do you enter code here?)

Type the code into the dialog box. Select all the code and click on the # above.
Or use the tags like
[code] your code [/code]

You mention wanting to add a date and a time. What form of result do you want, array? values in cells?

 

[Capell]

Type the code into the dialog box. Select all the code and click on the # above.
Or use the tags like
[code] your code [/code]

I've tried your suggestion aboutcde but it doesn't work. I'm also having difficulty typing into this thread because f dropped key presses. Why? My connectin speed is 220 Mbps.

 

[Peter_SSs]

I've tried your suggestion aboutcde but it doesn't work. I'm also having difficulty typing into this thread because f dropped key presses. Why? My connectin speed is 220 Mbps.

See if this thread (& related links within that thread) in the About This Board forum could apply to you.

What browser are you using?

 

[Capell]

That was fast!! IE11.

Capell