Concatenate Multiple Adjacent Cells In Same Row to Another Cell in Row (VBA)

[jgsimek]

I'm looking to concatenate the values in the same row for two separate columns, e.g., A1 and B1, into column C1, and to complete this task whenever values change in an identified range, e.g., A1:B10. I will in turn use the concatenated values in column C in my code, e.g., using vlookup. I understand that I can do this without VBA, however I need help coding in VBA please.

One or both of the values in cells A1 and B1 could be blank at any given time and I seem to be getting errors. Here's what I have so far. Any help is appreciated. Thanks! I'm sure I'm missing a lot and am happy to answer any questions.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c, changedRange as Range
Dim testVal1, testVal2, Concat as String

Const myRange = "A1:B10"
Const concatRange= "C1:C10"

Set changedRange = Intersect (Target, Range(myRange))

If Not changedRange Is Nothing Then
For each c in changedRange
testVal1 = c.Value
testVal2 = c.Value
Concat = testVal1 & testVal2
Range(concatRange).Value = Concat
Next c
End if

End sub

 

[hiker95]

jgsimek,

Welcome to the MrExcel forum.

You did not give examples of what is being entered into range A1:B10.

In the below screenshot I have range C1:C10 formatted as text:

Excel 2007
	A	B	C
1	111		111
2		222	222
3	333	444	333444
4	inter		inter
5		sect	sect
6	inter	sect	intersect
7	under		under
8		stand	stand
9	under	stand	understand
10	10	321	10321
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 05/19/2013
' http://www.mrexcel.com/forum/excel-questions/703465-concatenate-multiple-adjacent-cells-same-row-another-cell-row-visual-basic-applications.html
If Intersect(Target, Range("A1:B10")) Is Nothing Then Exit Sub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Cells(Target.Row, 3) = Cells(Target.Row, 1) & Cells(Target.Row, 2)
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then make changes to range A1:B10.

 

[Rick Rothstein]

Here is a link to a flexible concatenation function, which can be used as a UDF (user defined function) if desired, that I posted in a mini-blog article here...

Flexible Concatenation Function

 

[jgsimek]

Thank you both - works great!

 

[hiker95]

jgsimek,

You are very welcome. Glad we could help.

Come back anytime.