VBA: concatenate data

azov5

New Member
Joined
Dec 27, 2018
Messages
29
I have data in Column A1:A100 and B1:B100.
I need to Concatenate data in C column so that column A data appears below column B data in respective cell.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,691
Not clear to me what you want. Can you post a small sample of the data and how it would look after the VBA is executed?
 

azov5

New Member
Joined
Dec 27, 2018
Messages
29
Say if cell A2 is "where am I" and cell B2 data is "I am in USA" then the result in C2 will be

I am in USA
where am I

And this will follow up to C100
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,691
Try this:
Code:
Sub ConcatAandBinC()
Dim LRw As Long
LRw = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
With Range("C1:C" & LRw)
    .Formula = "=B1&CHAR(10)&A1"
    .Value = .Value
    .EntireColumn.AutoFit
End With
Application.ScreenUpdating = True
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,561
Office Version
2010
Platform
Windows
Since you indicated A1:A100 and B1:B100 all had data in them, this macro should work for you...
Code:
Sub ConcatAandB()
  [C1:C100] = [A1:A100&CHAR(10)&B1:B100]
End Sub
If the cells will not be filled down all the way to 100, then give this a try instead...
Code:
Sub ConcatAandB()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C1:C" & LastRow) = Evaluate(Replace("A1:A#&CHAR(10)&B1:B#", "#", LastRow))
End Sub
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,691
Since you indicated A1:A100 and B1:B100 all had data in them, this macro should work for you...
Code:
Sub ConcatAandB()
  [C1:C100] = [[COLOR=#ff0000][B]A1:A100&CHAR(10)&B1:B100[/B][/COLOR]]
End Sub
If the cells will not be filled down all the way to 100, then give this a try instead...
Code:
Sub ConcatAandB()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C1:C" & LastRow) = Evaluate(Replace("A1:A#&CHAR(10)&B1:B#", "#", LastRow))
End Sub
Rick, OP indicated he wants B-cell text above A-cell text.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,561
Office Version
2010
Platform
Windows
Rick, OP indicated he wants B-cell text above A-cell text.
Yikes! I completely missed that. :banghead: Thanks for pointing that out.

Simple fix though...

Since you indicated A1:A100 and B1:B100 all had data in them, this macro should work for you...
Code:
Sub ConcatAandB()
  [C1:C100] = [B1:B100&CHAR(10)&A1:A100]
End Sub
If the cells will not be filled down all the way to 100, then give this a try instead...
Code:
Sub ConcatAandB()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C1:C" & LastRow) = Evaluate(Replace("B1:B#&CHAR(10)&A1:A#", "#", LastRow))
End Sub
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,691
And if I want to do the opposite i.e, the original again !
Do you mean put the A-cell text above the B-cell text? If so, just swap A for B. In the code I posted it would be this line:

.Formula = "=B1&CHAR(10)&A1"

is changed to this:

.Formula = "=A1&CHAR(10)&B1"
 

Forum statistics

Threads
1,084,776
Messages
5,379,818
Members
401,629
Latest member
LEMANOIS

Some videos you may like

This Week's Hot Topics

Top