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

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
>
>

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
>
>

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
>
>