Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transpose array of cells (Excel 2000+)

    I couldn't find a nice transpose routine, so I wrote this one for Excel 2000.
    I should generalise it for all arrays e.g. Excel Formulae, Word tables etc.

    <pre>Public Function RotateDiagonal(rng As Range)
    Dim lngR As Long ' loop through rows
    Dim lngC As Long ' loop through columns
    ' Swap elements about, but not including, the diagonal
    Dim lngMax As Long
    lngMax = Application.WorksheetFunction.Max(rng.Rows.Count, rng.Columns.Count)
    For lngR = 1 To lngMax
    For lngC = 1 To lngR - 1
    Dim source
    source = rng.Cells(lngR, lngC).Value
    rng.Cells(lngR, lngC) = rng.Cells(lngC, lngR).Value
    rng.Cells(lngC, lngR) = source
    Next lngC
    Next lngR
    rng.Select
    'Sub TESTRotateDiagonal()
    ' Call RotateDiagonal(ActiveCell.CurrentRegion)
    'End Sub
    End Function</pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose array of cells (Excel 2000+)

    Thanks.

    One small suggestion: I'd remove the line

    rng.Select

    I generally prefer *not* to select ranges when using code. If necessary, the procedure that calls RotateDiagonal can select the original or transposed range.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose array of cells (Excel 2000+)

    >I generally prefer *not* to select ranges when using code.
    Thanks, Hans. Me too.
    It got left in there after my "debugging" view.
    Any end-user macros (as distinct from developer functions) based on this might leave the new (transposed) range selected as a guide for users.
    Why is Excel's Transpose function so {awkward|useless}?!!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose array of cells (Excel 2000+)

    TRANSPOSE is a worksheet function, to be used in cell formulas.
    A cell formula cannot change the shape/size of a range, and trying to use formulas to transpose a range "in place" would lead to circular references.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose array of cells (Excel 2000+)

    Transpose isn't useless at all!

    Check out:

    http://www.jkp-ads.com/Articles/FormulaTranspose.asp
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose array of cells (Excel 2000+)

    >Transpose isn't useless at all! Check out: http://www.jkp-ads.com/Articles/FormulaTranspose.asp
    It's not even 6am Monday morning and already I'm feeling small and insecure ... (grin!)

    Thanks Jan Karel!

Posting Permissions

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