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

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

On 6 July, 17:00, "Rick Rothstein"
wrote:
Clarification please... you say you have 2560 of these...

What I actually have to process is a spreadsheet full of local areas,
known as "wards". After each ward name is a postcode (Americans call
these "zip codes"), or usually several postcodes. A single row might
look like this:

Barton Ward CT6 5RT, CT6 4JL, CT6 7YY, CT6 8QW

That's one row. All the postcodes are in one cell, separated by
commas. In order to perform the look-up of the postcodes (on another
spreadsheet), I need it to look like:

Barton Ward CT6 5RT
Barton Ward CT6 4JL
Barton Ward CT6 7YY
Barton Ward CT6 8QW

The first bit is easy - I used Excel's Text-to-Columns feature to
split the postcodes into separate columns, and then I remove the
leading spaces (using a macro I already have).

So now I have:

Barton Ward CT6 5RT CT6 4JL CT6 7YY CT6 8QW

.... and I need ...

Barton Ward CT6 5RT
Barton Ward CT6 4JL
Barton Ward CT6 7YY
Barton Ward CT6 8QW

I am aware that I could do this manually by inserting 3 more rows
underneath my one; then copying the last three postcodes, and doing
PasteSpecial (with Transpose) underneath CT6 5RT. Then copying
"Barton Ward" down three times.

However, with 2,000+ of these to do, I thought a VBA solution would
save a lot of time.

I'm just having trouble using the PasteSpecial feature in VBA,
really. The selection, inserting rows and copying work fine. But the
PasteSpecial gives an error. I was also wondering whether there might
be a more efficient, or easier, method than using PasteSpecial.

Can you suggest any method?

Steve

One way that requires xl2k or higher:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim RptWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Dim DestCell As Range
Dim HowManyRows As Long

Dim mySplit As Variant
Dim pCtr As Long

Set CurWks = Worksheets("Sheet1")
Set RptWks = Worksheets.Add
Set DestCell = RptWks.Range("A1")

With CurWks
FirstRow = 1 'no headers???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
mySplit = Split(.Cells(iRow, "B").Value, ",")
For pCtr = LBound(mySplit) To UBound(mySplit)
mySplit(pCtr) = Trim(mySplit(pCtr))
Next pCtr

HowManyRows = UBound(mySplit) - LBound(mySplit) + 1

If HowManyRows 0 Then
DestCell.Resize(HowManyRows, 1).Value _
= .Cells(iRow, "A").Value
DestCell.Offset(0, 1).Resize(HowManyRows, 1).Value _
= Application.Transpose(mySplit)
Set DestCell = DestCell.Offset(HowManyRows, 0)
End If
Next iRow
End With
End Sub

I would assume that you're going to look at the code and return the ward name
using =match().

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

I included Debra's page for =vlookup() if you decide to put the codes in column
A and the wards in column B.

wrote:

On 6 July, 17:00, "Rick Rothstein"
wrote:
Clarification please... you say you have 2560 of these...

What I actually have to process is a spreadsheet full of local areas,
known as "wards". After each ward name is a postcode (Americans call
these "zip codes"), or usually several postcodes. A single row might
look like this:

Barton Ward CT6 5RT, CT6 4JL, CT6 7YY, CT6 8QW

That's one row. All the postcodes are in one cell, separated by
commas. In order to perform the look-up of the postcodes (on another
spreadsheet), I need it to look like:

Barton Ward CT6 5RT
Barton Ward CT6 4JL
Barton Ward CT6 7YY
Barton Ward CT6 8QW

The first bit is easy - I used Excel's Text-to-Columns feature to
split the postcodes into separate columns, and then I remove the
leading spaces (using a macro I already have).

So now I have:

Barton Ward CT6 5RT CT6 4JL CT6 7YY CT6 8QW

... and I need ...

Barton Ward CT6 5RT
Barton Ward CT6 4JL
Barton Ward CT6 7YY
Barton Ward CT6 8QW

I am aware that I could do this manually by inserting 3 more rows
underneath my one; then copying the last three postcodes, and doing
PasteSpecial (with Transpose) underneath CT6 5RT. Then copying
"Barton Ward" down three times.

However, with 2,000+ of these to do, I thought a VBA solution would
save a lot of time.

I'm just having trouble using the PasteSpecial feature in VBA,
really. The selection, inserting rows and copying work fine. But the
PasteSpecial gives an error. I was also wondering whether there might
be a more efficient, or easier, method than using PasteSpecial.

Can you suggest any method?

Steve

--

Dave Peterson

Thank you, Dave. I am off work for a few days, but as soon as I
return I will try out your suggested code.

Steve

mySplit = Split(.Cells(iRow, "B").Value, ",")
For pCtr = LBound(mySplit) To UBound(mySplit)
mySplit(pCtr) = Trim(mySplit(pCtr))
Next pCtr

Another way to do this part of your code would be with this single line of
code...

mySplit = Split(Replace(.Cells(iRow, "B").Value, ", ", ","), ",")

Unless you are thinking there could be some cases where there are more than
a single space character after a comma (in which case your code would be the
way to handle it).

By the way, for those reading this thread who do not like functional steps
embedded within other functional steps, the one-liner statement above just
takes these two lines of code and combines them...

mySplit = Replace(.Cells(iRow, "B").Value, ", ", ",")
mySplit = Split(mySplit, ",")

I would also point out that IF the data is know to be "pure" (that is, it is
known that there is **always** a single space after the comma), then the
Replace function call is not needed... we could just use a comma/space pair
of characters as the delimiter like this...

mySplit = Split(.Cells(iRow, "B").Value, ", ")

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
One way that requires xl2k or higher:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim RptWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Dim DestCell As Range
Dim HowManyRows As Long

Dim mySplit As Variant
Dim pCtr As Long

Set CurWks = Worksheets("Sheet1")
Set RptWks = Worksheets.Add
Set DestCell = RptWks.Range("A1")

With CurWks
FirstRow = 1 'no headers???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
mySplit = Split(.Cells(iRow, "B").Value, ",")
For pCtr = LBound(mySplit) To UBound(mySplit)
mySplit(pCtr) = Trim(mySplit(pCtr))
Next pCtr

HowManyRows = UBound(mySplit) - LBound(mySplit) + 1

If HowManyRows 0 Then
DestCell.Resize(HowManyRows, 1).Value _
= .Cells(iRow, "A").Value
DestCell.Offset(0, 1).Resize(HowManyRows, 1).Value _
= Application.Transpose(mySplit)
Set DestCell = DestCell.Offset(HowManyRows, 0)
End If
Next iRow
End With
End Sub

I would assume that you're going to look at the code and return the ward
name
using =match().

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

I included Debra's page for =vlookup() if you decide to put the codes in
column
A and the wards in column B.

wrote:

On 6 July, 17:00, "Rick Rothstein"
wrote:
Clarification please... you say you have 2560 of these...

What I actually have to process is a spreadsheet full of local areas,
known as "wards". After each ward name is a postcode (Americans call
these "zip codes"), or usually several postcodes. A single row might
look like this:

Barton Ward CT6 5RT, CT6 4JL, CT6 7YY, CT6 8QW

That's one row. All the postcodes are in one cell, separated by
commas. In order to perform the look-up of the postcodes (on another
spreadsheet), I need it to look like:

Barton Ward CT6 5RT
Barton Ward CT6 4JL
Barton Ward CT6 7YY
Barton Ward CT6 8QW

The first bit is easy - I used Excel's Text-to-Columns feature to
split the postcodes into separate columns, and then I remove the
leading spaces (using a macro I already have).

So now I have:

Barton Ward CT6 5RT CT6 4JL CT6 7YY CT6 8QW

... and I need ...

Barton Ward CT6 5RT
Barton Ward CT6 4JL
Barton Ward CT6 7YY
Barton Ward CT6 8QW

I am aware that I could do this manually by inserting 3 more rows
underneath my one; then copying the last three postcodes, and doing
PasteSpecial (with Transpose) underneath CT6 5RT. Then copying
"Barton Ward" down three times.

However, with 2,000+ of these to do, I thought a VBA solution would
save a lot of time.

I'm just having trouble using the PasteSpecial feature in VBA,
really. The selection, inserting rows and copying work fine. But the
PasteSpecial gives an error. I was also wondering whether there might
be a more efficient, or easier, method than using PasteSpecial.

Can you suggest any method?

Steve

--

Dave Peterson

And using application.trim() would solve any multiple embedded spaces.

But I'm not sure if any code has multiple embedded spaces--so I chose not to use
it.

Rick Rothstein wrote:

mySplit = Split(.Cells(iRow, "B").Value, ",")
For pCtr = LBound(mySplit) To UBound(mySplit)
mySplit(pCtr) = Trim(mySplit(pCtr))
Next pCtr

Another way to do this part of your code would be with this single line of
code...

mySplit = Split(Replace(.Cells(iRow, "B").Value, ", ", ","), ",")

Unless you are thinking there could be some cases where there are more than
a single space character after a comma (in which case your code would be the
way to handle it).

By the way, for those reading this thread who do not like functional steps
embedded within other functional steps, the one-liner statement above just
takes these two lines of code and combines them...

mySplit = Replace(.Cells(iRow, "B").Value, ", ", ",")
mySplit = Split(mySplit, ",")

I would also point out that IF the data is know to be "pure" (that is, it is
known that there is **always** a single space after the comma), then the
Replace function call is not needed... we could just use a comma/space pair
of characters as the delimiter like this...

mySplit = Split(.Cells(iRow, "B").Value, ", ")

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
One way that requires xl2k or higher:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim RptWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Dim DestCell As Range
Dim HowManyRows As Long

Dim mySplit As Variant
Dim pCtr As Long

Set CurWks = Worksheets("Sheet1")
Set RptWks = Worksheets.Add
Set DestCell = RptWks.Range("A1")

With CurWks
FirstRow = 1 'no headers???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
mySplit = Split(.Cells(iRow, "B").Value, ",")
For pCtr = LBound(mySplit) To UBound(mySplit)
mySplit(pCtr) = Trim(mySplit(pCtr))
Next pCtr

HowManyRows = UBound(mySplit) - LBound(mySplit) + 1

If HowManyRows 0 Then
DestCell.Resize(HowManyRows, 1).Value _
= .Cells(iRow, "A").Value
DestCell.Offset(0, 1).Resize(HowManyRows, 1).Value _
= Application.Transpose(mySplit)
Set DestCell = DestCell.Offset(HowManyRows, 0)
End If
Next iRow
End With
End Sub

I would assume that you're going to look at the code and return the ward
name
using =match().

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

I included Debra's page for =vlookup() if you decide to put the codes in
column
A and the wards in column B.

wrote:

On 6 July, 17:00, "Rick Rothstein"
wrote:
Clarification please... you say you have 2560 of these...

What I actually have to process is a spreadsheet full of local areas,
known as "wards". After each ward name is a postcode (Americans call
these "zip codes"), or usually several postcodes. A single row might
look like this:

Barton Ward CT6 5RT, CT6 4JL, CT6 7YY, CT6 8QW

That's one row. All the postcodes are in one cell, separated by
commas. In order to perform the look-up of the postcodes (on another
spreadsheet), I need it to look like:

Barton Ward CT6 5RT
Barton Ward CT6 4JL
Barton Ward CT6 7YY
Barton Ward CT6 8QW

The first bit is easy - I used Excel's Text-to-Columns feature to
split the postcodes into separate columns, and then I remove the
leading spaces (using a macro I already have).

So now I have:

Barton Ward CT6 5RT CT6 4JL CT6 7YY CT6 8QW

... and I need ...

Barton Ward CT6 5RT
Barton Ward CT6 4JL
Barton Ward CT6 7YY
Barton Ward CT6 8QW

I am aware that I could do this manually by inserting 3 more rows
underneath my one; then copying the last three postcodes, and doing
PasteSpecial (with Transpose) underneath CT6 5RT. Then copying
"Barton Ward" down three times.

However, with 2,000+ of these to do, I thought a VBA solution would
save a lot of time.

I'm just having trouble using the PasteSpecial feature in VBA,
really. The selection, inserting rows and copying work fine. But the
PasteSpecial gives an error. I was also wondering whether there might
be a more efficient, or easier, method than using PasteSpecial.

Can you suggest any method?

Steve

--

Dave Peterson

--

Dave Peterson

The first bit is easy - I used Excel's Text-to-Columns feature to
split the postcodes into separate columns, and then I remove the
leading spaces (using a macro I already have).

The code Dave has given you will handle all of the above functionality for
you (actually, it eliminates the need to do the Text-To-Columns part
completely), but I just thought I would mention the following for you future
reference. Assuming your post codes are *always* delimited by a comma/space
(and never comma/multiple spaces), then instead of using a macro to remove
the leading spaces that result from the Text-To-Columns operation, you could
have done an Edit/Replace All on the original data first and then perform
the Text-To-Columns on that modified data... this way there would not have
been any leading spaces to need a macro for.

--
Rick (MVP - Excel)


wrote in message
...
On 6 July, 17:00, "Rick Rothstein"
wrote:
Clarification please... you say you have 2560 of these...

What I actually have to process is a spreadsheet full of local areas,
known as "wards". After each ward name is a postcode (Americans call
these "zip codes"), or usually several postcodes. A single row might
look like this:

Barton Ward CT6 5RT, CT6 4JL, CT6 7YY, CT6 8QW

That's one row. All the postcodes are in one cell, separated by
commas. In order to perform the look-up of the postcodes (on another
spreadsheet), I need it to look like:

Barton Ward CT6 5RT
Barton Ward CT6 4JL
Barton Ward CT6 7YY
Barton Ward CT6 8QW

The first bit is easy - I used Excel's Text-to-Columns feature to
split the postcodes into separate columns, and then I remove the
leading spaces (using a macro I already have).

So now I have:

Barton Ward CT6 5RT CT6 4JL CT6 7YY CT6 8QW

... and I need ...

Barton Ward CT6 5RT
Barton Ward CT6 4JL
Barton Ward CT6 7YY
Barton Ward CT6 8QW

I am aware that I could do this manually by inserting 3 more rows
underneath my one; then copying the last three postcodes, and doing
PasteSpecial (with Transpose) underneath CT6 5RT. Then copying
"Barton Ward" down three times.

However, with 2,000+ of these to do, I thought a VBA solution would
save a lot of time.

I'm just having trouble using the PasteSpecial feature in VBA,
really. The selection, inserting rows and copying work fine. But the
PasteSpecial gives an error. I was also wondering whether there might
be a more efficient, or easier, method than using PasteSpecial.

Can you suggest any method?

Steve