Transpose a range in VBA

Question

I am Trying to Transpose a range of cells in Excel through VBA macro but I am getting some errors  mostly Error 91    I am pretty new to VBA and don t have much idea about functions either   Range InRng  Select Set Range1   Selection Dim DestRange As Range Set DestRange   Application WorksheetFunction Transpose Range1    After going through a couple of forums  this is what I have come up with  One thing to note is that I don t have to copy them into any other cells    What I am trying to achieve is to create a co-variance method and in the option window the user will have the option to select the range and then choose either by columns or rows  this will then affect the resulting covariance matrix

User · Answer

Something like this should do it for you.

Sub CombineColumns1()
    Dim xRng As Range
    Dim i As Long, j As Integer
    Dim xNextRow As Long
    Dim xTxt As String
    On Error Resume Next
    With ActiveSheet
        xTxt = .RangeSelection.Address
        Set xRng = Application.InputBox("please select the data range", "Kutools for Excel", xTxt, , , , , 8)
        If xRng Is Nothing Then Exit Sub
        j = xRng.Columns(1).Column
        For i = 4 To xRng.Columns.Count Step 3
            'Need to recalculate the last row, as some of the final columns may not have data in all rows
            xNextRow = .Cells(.Rows.Count, j).End(xlUp).Row + 1

            .Range(xRng.Cells(1, i), xRng.Cells(xRng.Rows.Count, i + 2)).Copy .Cells(xNextRow, j)
            .Range(xRng.Cells(1, i), xRng.Cells(xRng.Rows.Count, i + 2)).Clear
        Next
    End With
End Sub

You could do this too.

Sub TransposeFormulas()
    Dim vFormulas As Variant
    Dim oSel As Range
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a range of cells first.", _
                vbOKOnly + vbInformation, "Transpose formulas"
        Exit Sub
    End If
    Set oSel = Selection
    vFormulas = oSel.Formula
    vFormulas = Application.WorksheetFunction.Transpose(vFormulas)
    oSel.Offset(oSel.Rows.Count + 2).Resize(oSel.Columns.Count, oSel.Rows.Count).Formula = vFormulas
End Sub

See this for more info.

http://bettersolutions.com/vba/arrays/transposing.htm

User · Answer

First copy the source range then paste-special on target range with Transpose  True  short sample   Option Explicit  Sub test     Dim sourceRange As Range   Dim targetRange As Range    Set sourceRange   ActiveSheet Range Cells 1  1   Cells 5  1     Set targetRange   ActiveSheet Cells 6  1     sourceRange Copy   targetRange PasteSpecial Paste  xlPasteValues  Operation  xlNone  SkipBlanks  False  Transpose  True End Sub   The Transpose function takes parameter of type Varaiant and returns Variant      Sub transposeTest       Dim transposedVariant As Variant     Dim sourceRowRange As Range     Dim sourceRowRangeVariant As Variant      Set sourceRowRange   Range  A1 H1     one row  eight columns     sourceRowRangeVariant   sourceRowRange Value     transposedVariant   Application Transpose sourceRowRangeVariant       Dim rangeFilledWithTransposedData As Range     Set rangeFilledWithTransposedData   Range  I1 I8     eight rows  one column     rangeFilledWithTransposedData Value   transposedVariant   End Sub   I will try to explaine the purpose of  calling transpose twice    If u have row data in Excel e g   a1 h1  then the Range  a1 h1   Value is a 2D Variant-Array with dimmensions 1 to 1  1 to 8   When u call Transpose Range  a1 h1   Value  then u get transposed 2D Variant Array with dimensions 1 to 8  1 to 1  And if u call Transpose Transpose Range  a1 h1   Value   u get 1D Variant Array with dimension 1 to 8    First Transpose changes row to column and second transpose changes the column back to row but with just one dimension   If the source range would have more rows  columns  e g   a1 h3  then Transpose function just changes the dimensions like this  1 to 3  1 to 8 Transposes to 1 to 8  1 to 3 and vice versa    Hope i did not confuse u  my english is bad  sorry  -

User · Answer

This gets you X and X  as variant arrays you can pass to another function   Dim X   As Variant Dim XT   As Variant X   ActiveSheet Range  InRng   Value2 XT   Application Transpose X    To have the transposed values as a range  you have to pass it via a worksheet as in this answer  Without seeing how your covariance function works it s hard to see what you need

User · Answer

Strictly in reference to prefacing  transpose   by the book  either one will work  i e   application transpose   OR worksheetfunction transpose    and by experience  if you really like typing  application WorksheetFunction Transpose   will work also-

User · Answer

You do not need to do this  Here is how to create a co-variance method   http   www youtube com watch v RqAfC4JXd4A  Alternatively you can use statistical analysis package that Excel has

[vba] Transpose a range in VBA

Examples related to vba

Examples related to excel