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

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

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

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

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  -

[vba] Transpose a range in VBA

Examples related to vba

Examples related to excel