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

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

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

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

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

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   lt  gt   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

[vba] Transpose a range in VBA

Examples related to vba

Examples related to excel