look down column and paste values across cells

[wibbers2000]

Hi,
I run a report and it displays in rows down but I would like to run it across.


ie. columns
A B
1525 deliver
1525 base
1525 install
2569 base
2569 deliver
3652 base
5482 deliver
5482 install

I would like it to appear
A B C D
1525 deliver base install
2569 base deliver
3652 base
5482 deliver install


is this possible in VBA?

Regards
Paul

[JimmyTheHand]

Hi Paul,

In VBA almost everything is possible.
Try the sub below. It's designed to work if, before running it, you activate the top left cell of the range, i.e. the first 1525 in column A. It is also required that the range in column A be contiguous, and that there be always a value in column B.
Of course, these limitations can be eliminated, but for that I need more info about the layout of the worksheet.

[vba]Sub TransP()
Dim RefCel As Range
Set RefCel = ActiveCell
Do While RefCel.Offset(1) <> ""
If RefCel.Offset(1) = RefCel Then
RefCel.End(xlToRight).Offset(, 1) = RefCel.Offset(1, 1)
RefCel.Offset(1).Resize(, 2).Delete xlUp
Else
Set RefCel = RefCel.Offset(1)
End If
Loop
End Sub
[/vba]

Jimmy