![]() |
Merge the contents of three non-adjacent columns
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 |
Merge the contents of three non-adjacent columns
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 On May 3, 4:31 pm, "Walt" wrote: 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" wrote in message oups.com... 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- Hide quoted text - - Show quoted text - |
All times are GMT +1. The time now is 03:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com