Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose cells - do I use Paste Special?
I have a data spreadsheet which has some postcodes that need to be
"moved around". They are currently like this (typical example): CT10 3DD CT10 3DF CT10 3DG CT10 3DQ CT10 3DY CT10 3EJ .... spread across six cells, and I need them to go downwards, like this: CT10 3DD CT10 3DF CT10 3DG CT10 3DQ CT10 3DY CT10 3EJ Now, this is easy to do manually, using Paste Special with "Transpose" - but I have around 2,560 of them (complete with other address details), so I thought I'd use a macro. I thought that if I selected the postcodes in the row first, a macro could count the number of cells in my selection, then insert rows beneath my selection to accommodate the postcodes once PasteSpecial'ed in (and not overwrite the data below). So far I have come up with: Sub Macro1() cCount = Selection.Cells.Count For i = 1 To cCount - 1 ActiveCell.Offset(1).EntireRow.Insert Next End Sub This works, but when I add: Selection.Copy Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True .... it does not work, because I am pasting over the top of one of the cells I already have selected. Is PasteSpecial the way to go here? Is there no way I could take the original contents of Selection.Cells into memory (perhaps as an array), then tranpose them by inserting each element of the array into cells going downwards? Just a thought. Or is there a different way of using PasteSpecial that I have overlooked or misunderstood? Thank you for any advice you could give. Steve Wylie |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose, Paste Special | Excel Discussion (Misc queries) | |||
Copy Paste Special Transpose Skip Cells and start over | Excel Programming | |||
Paste Special / transpose | Excel Discussion (Misc queries) | |||
When I special paste and transpose.... | New Users to Excel | |||
Paste Special Transpose | Excel Programming |