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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose cells - do I use Paste Special?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose cells - do I use Paste Special?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose cells - do I use Paste Special?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose cells - do I use Paste Special?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose cells - do I use Paste Special?
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose cells - do I use Paste Special?
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose cells - do I use Paste Special?
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose cells - do I use Paste Special?
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose cells - do I use Paste Special?
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 |
Reply |
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 |