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