VBA: concatenate data

[azov5]

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]

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]

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]

Try this:

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]

Since you indicated A1:A100 and B1:B100 all had data in them, this macro should work for you...

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...

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

 

[JoeMo]

Since you indicated A1:A100 and B1:B100 all had data in them, this macro should work for you...

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...

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]

Rick, OP indicated he wants B-cell text above A-cell text.

Yikes! I completely missed that. 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...

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...

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

 

[azov5]

And if I want to do the opposite i.e, the original again !

 

[Rick Rothstein]

And if I want to do the opposite i.e, the original again !

What do you mean by this? Isn't the code that was provided what you asked for?

 

[JoeMo]

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"