在 VBA 中转置范围

Transpose a range in VBA

提问by Abhi.Net

I am Trying to Transpose a range of cells in Excel through VBA macro but I am getting some errors, mostly Error 91.

我正在尝试通过 VBA 宏在 Excel 中转置一系列单元格，但出现一些错误，主要是错误 91。

I am pretty new to VBA and don't have much idea about functions either.

我对 VBA 很陌生，对函数也不太了解。

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.

我想要实现的是创建一个协方差方法，在选项窗口中，用户可以选择范围，然后按列或行进行选择，这将影响生成的协方差矩阵。

回答by Jamie Bull

This gets you X and X' as variant arrays you can pass to another function.

这让你 X 和 X' 作为可变数组，你可以传递给另一个函数。

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.

要将转置值作为范围，您必须通过工作表传递它，如本答案所示。如果不了解协方差函数的工作原理，就很难看出您需要什么。

回答by dee

First copy the source range then paste-special on target range with Transpose:=True, short sample:

首先复制源范围，然后使用 Transpose:=True 在目标范围上进行特殊粘贴，短样本：

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.

Transpose 函数采用 Variant 类型的参数并返回 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 datain Excel e.g. "a1:h1"then the Range("a1:h1").Valueis a 2D Variant-Arraywith dimmensions 1 to 1, 1 to 8. When u call Transpose(Range("a1:h1").Value)then u get transposed 2D Variant Arraywith dimensions 1 to 8, 1 to 1. And if u call Transpose(Transpose(Range("a1:h1").Value))u get 1D Variant Arraywith dimension 1 to 8.

我将尝试解释“两次调用转置”的目的。如果您在 Excel 中有行数据，例如"a1:h1"那么Range("a1:h1").Value是一个二维 Variant-Array，维度为1 到 1, 1 到 8。当您调用Transpose(Range("a1:h1").Value) 时，您会得到尺寸为1 到 8、1 到 1 的转置二维变体数组。如果你调用Transpose(Transpose(Range("a1:h1").Value))你会得到维度为1 到 8 的一维 Variant Array 。

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.

如果源范围有更多的行（列），例如“a1:h3”，那么转置函数只是改变维度，如下所示：1 到 3、1 到 8 转置到 1 到 8、1 到 3，反之亦然。

Hope i did not confuse u, my english is bad, sorry :-).

希望我没有混淆你，我的英语不好，对不起:-)。

回答by Margus

You do not need to do this. Here is how to create a co-variance method:

您不需要这样做。以下是创建协方差方法的方法：

http://www.youtube.com/watch?v=RqAfC4JXd4A

http://www.youtube.com/watch?v=RqAfC4JXd4A

Alternatively you can use statistical analysis package that Excel has.

或者，您可以使用 Excel 具有的统计分析包。

回答by user3424185

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-

严格参考前面提到的“转置”，书中的任何一个都可以；即 application.transpose() 或 worksheetfunction.transpose()，根据经验，如果您真的喜欢打字，application.WorksheetFunction.Transpose() 也可以-

回答by ASH

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

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