+ Reply to Thread
Results 1 to 6 of 6

Tranposing an array of cells

  1. #1
    Forum Contributor
    Join Date
    07-29-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    365
    Posts
    255

    Tranposing an array of cells

    hi there

    Looking to take an array of cells and paste them from left to right as right to left.

    for example if I have 3,2,4 and want them to be 4,2,3.

    The paste special option will make them vertical and then horizontally but not how I am expecting them to be.
    Last edited by pickslides; 09-29-2009 at 11:29 PM.
    Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Tranposing an array of cells

    Hello PickSlides,

    Just a few questions:
    Are you looking for a worksheet formula or VBA to do this?
    Will the range to be transposed be a single row?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor
    Join Date
    07-29-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    365
    Posts
    255

    Re: Tranposing an array of cells

    Worksheet formula would be preffered.

    Yes, it is a single row.

  4. #4
    Registered User
    Join Date
    09-28-2009
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Tranposing an array of cells

    I used the "Record macro" to perform this, I'm sure there's gotta be an easier way to do it. See attachment for the example, simply input values/data on the 1st input sheet and hit "run" button, result will then pop on the 3rd sheet.
    Hope it helps. Thanks.

    Sub All()
    '
    ' All Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+A
    '
    Range("A1:C1").Select
    Selection.Copy
    Sheets("Transpose").Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "3"
    Columns("A:A").Select
    ActiveWorkbook.Worksheets("Transpose").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Transpose").Sort.SortFields.Add Key:=Range("A1"), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Transpose").Sort
    .SetRange Range("A1:B3")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Range("B1:B3").Select
    Selection.Copy
    Sheets("Output").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    End Sub
    Attached Files Attached Files
    Last edited by bsengineer; 09-28-2009 at 09:25 PM.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Tranposing an array of cells

    Worksheet formula would be preffered.
    The method you should opt for would I think depend largely on the initial range and whether or not it is always a fixed size etc... if it is only ever A1:C1 as outlined then to get Ouput!A1:C1 to display Input!C1:A1

    Output!A1:
    =INDEX(Input!$A$1:$C$1,4-COLUMNS($A1:A1))
    copied to C1

  6. #6
    Forum Contributor
    Join Date
    07-29-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    365
    Posts
    255

    Re: Tranposing an array of cells

    Thank you both.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1