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.

Answer 1

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.

Answer 2

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 :-).