Transpose cells - do I use Paste Special?

[stevewy]

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

 

[Rick Rothstein]

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

 

[stevewy]

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]

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.

 

[stevewy]

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

 

[Rick Rothstein]

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

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.

 

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

 

[stevewy]

Okay, thank you for the additional comments. In actual fact, I
already had done Text-to-Columns to convert the comma-delimited data
to separate columns; so it is just the "moving the postcodes around"
part of Dave's solution I will be trying to extract when I get to
work.

I'll post back on Friday to let you know how I got on.

Steve

 

[stevewy]

I finally got around to sorting this out.

I am trying unsuccessfully to adapt Dave's macro to my particular
circumstances. The spreadsheet I have at the moment already has the
postcodes separated, and I just need to Transpose them into "going
down" rather than "going across".

Here is an actual extract from the first bit of the spreadsheet:

Postal Address Ward All Post Codes
Broadstairs, Kent Beacon Road CT10 3DT
Broadstairs, Kent Beacon Road CT10 3HY
Broadstairs, Kent Beacon Road CT10 3BB
Broadstairs, Kent Beacon Road CT10 3AA CT10 3AB CT10 3AD
Broadstairs, Kent Beacon Road CT10 3EH
Broadstairs, Kent Beacon Road
Broadstairs, Kent Beacon Road CT10 3DD CT10 3DF CT10 3DG CT10 3DQ CT10
3DY CT10 3EJ
Broadstairs, Kent Beacon Road CT10 3DD CT10 3DF CT10 3DG CT10 3DQ CT10
3DY CT10 3EJ

In actual fact, Postal Address is in column B. Column A contains some
other road names, but when I paste it into the message it goes all
over the screen, so I have omitted it.

The first three rows don't need to be changed. Row 4 needs to have
the first two columns repeated an additional two times below and CT10
3AB and CT10 3AD transposed to run downwards in column 3.

What I cannot work out from your initial code is how to get round the
macro's reliance on "mySplit", which presumably has reference to
information picked up during the splitting phase of your macro. As I
am bypassing this, I need the macro to do its duplication of rows
based on how far the postcodes stretch across the spreadsheet in
columns. I initially thought of doing this by selecting to the end of
the row then measuring how many cells I had selected. Then I ran
across the "Paste Transpose" problem.

Would it be possible, Dave, for you to tweak your macro to remove the
splitting part and for it to work on separate cells? I had a go, but
it has defeated me so far!

Thank you.

Steve

 

[stevewy]

It's all right - I think I have cobbled something together to complete
this task. It's just a basic "move the cells around":

Sub MM()
Range(Selection, Selection.End(xlToRight)).Select
num = Selection.Cells.Count
For i = 1 To num - 1
ActiveCell.Offset(1).EntireRow.Insert
Next
For i = 1 To num - 1
ActiveCell.Offset(i, 0) = ActiveCell.Offset(0, i)
ActiveCell.Offset(0, i) = Null
Next
End Sub

I can easily run through it afterwards filling in duplicate values in
the address fields where necessary.

The initial "complete" macro by Dave Peterson is excellent and self-
contained though - and I shall certainly hang onto it for use when a
similar grotty list of postcodes & addresses comes down again for
processing.

Thank you, Rick and Dave, for your time and assistance in this matter.

Steve