Concatenate list of cell values into one cell
Hi,
I have a list of data in column A, like the following; UAD54334 UAD54354 UAD97721 UAD31225 and so on... I would like to merge all these values into one cell, but with | seperating each value. ie. I would like cell B2 to display "UAD54334| UAD54354|UAD97721|UAD31225" The concatenate function would work fine, but I would have to quote each cell reference from my list each time. I'm not sure how to merge data into one cell using VBA. Some advice would be appreciated. Thanks Tony |
Concatenate list of cell values into one cell
Sub ABCD()
Dim rng As Range, cell1 As Range Dim cell As Range Set rng = Selection Set cell1 = rng(1) Set rng = rng.Offset(1, 0).Resize(rng.Count - 1, 1) For Each cell In rng cell1.Value = cell1 & "|" & cell Next rng.ClearContents End Sub Select your data an run the macro. It concatenates it into the first cell in the selection. Will only work on a single area range one column wide with at least two rows selected (like your example) -- Regards, Tom Ogilvy "bony_tony" wrote: Hi, I have a list of data in column A, like the following; UAD54334 UAD54354 UAD97721 UAD31225 and so on... I would like to merge all these values into one cell, but with | seperating each value. ie. I would like cell B2 to display "UAD54334| UAD54354|UAD97721|UAD31225" The concatenate function would work fine, but I would have to quote each cell reference from my list each time. I'm not sure how to merge data into one cell using VBA. Some advice would be appreciated. Thanks Tony |
Concatenate list of cell values into one cell
Try this:-
Sub stantiate() Dim myrange As Range With ActiveSheet lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row End With Set myrange = Range("A1:A" & lastrow) For Each c In myrange Cells(1, 2).Value = Cells(1, 2).Value & c.Value & "|" Next End Sub Works on the active worksheet. Mike "bony_tony" wrote: Hi, I have a list of data in column A, like the following; UAD54334 UAD54354 UAD97721 UAD31225 and so on... I would like to merge all these values into one cell, but with | seperating each value. ie. I would like cell B2 to display "UAD54334| UAD54354|UAD97721|UAD31225" The concatenate function would work fine, but I would have to quote each cell reference from my list each time. I'm not sure how to merge data into one cell using VBA. Some advice would be appreciated. Thanks Tony |
Concatenate list of cell values into one cell
after further reading, I see you want the results in another location. So
the modification would be Sub ABCD() Dim rng As Range, cell1 As Range Dim cell As Range Set rng = Selection Set cell1 = Range("B2") cell1.clearcontents For Each cell In rng cell1.Value = cell1 & "|" & cell Next End Sub -- Regards, Tom Ogilvy "bony_tony" wrote: Hi, I have a list of data in column A, like the following; UAD54334 UAD54354 UAD97721 UAD31225 and so on... I would like to merge all these values into one cell, but with | seperating each value. ie. I would like cell B2 to display "UAD54334| UAD54354|UAD97721|UAD31225" The concatenate function would work fine, but I would have to quote each cell reference from my list each time. I'm not sure how to merge data into one cell using VBA. Some advice would be appreciated. Thanks Tony |
All times are GMT +1. The time now is 08:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com