Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If a cell matches a list of values...... | Excel Discussion (Misc queries) | |||
Concatenate text cell and formula cell result | Excel Worksheet Functions | |||
Creating a list of cell values | New Users to Excel | |||
Combining cell values in a list | Excel Programming | |||
Want to be able to click on a cell and have a list box appear to give values to populate a cell | Excel Programming |