Merge the contents of three non-adjacent columns

[Sisilla]

Hello All,

I am trying to merge the contents of three non-adjacent columns (D, I
and N) into one column (C) on a worksheet (Data2).

Sheets("Data2").Range("C1").FormulaR1C1 = "=D1&I1&N1"
Sheets("Data2").Range("C1").AutoFill
Destination:=Sheets("Data2").Range("C1:C" & LastRow2),
Type:=xlFillDefault
Sheets("Data2").Cells.Replace What:="=", Replacement:="=",
LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

The code above sets the formula for C2 as "=D1&I1&N2", C3 as
"=D1&I1&N3", C4 as "=D1&I1&N4" and so on, but what I want is for C2's
formula to be set as "=D2&I2&N2", C3 as "=D3&I3&N3", C4 as "=D4&I4&N4"
and so on.

I would also like to insert line breaks to separate the merged data,
but I am not sure how to use Chr(10). I do not know any R1C1 notation,
and I have been unable to locate help documentation for it.

If anyone could point me in the right direction, I would be very
grateful. Thank you for your time and consideration.

Sincerely,
Sisilla

 

[Walt]

You could use the Concatenate function and not use code. Enter
"=CONCATENATE(D2," ",I2," ",N2)" in cell C2 and copy down. This version puts
a space between the contents of the cells. If you don't want the space,
leave out the " " parts.

Walt

 

[Sisilla]

Thank you very much for your response, Walt. I am afraid that I do
need to do this in VBA. I was trying to avoid looping through each row
on the worksheet, but it seems this is the only solution that I can
come up with.

Again Thanks,
Sisilla