Clarification please... you say you have 2560 of these... all in one row
(meaning you are using XL2007) or on several row (which would be necessary
in any lesser version of Excel? If on several rows, where did you want the
insertions/transposed data at... in between each of these rows or collect at
the end of the postcode rows (and before some assumed other data)?

As an aside, this code...

For i = 1 To cCount - 1
ActiveCell.Offset(1).EntireRow.Insert
Next

can be replaced by this single line of code...

ActiveCell.Resize(cCount - 1).EntireRow.Insert

--
Rick (MVP - Excel)


wrote in message
...
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