VBA excel宏转置特定矩阵
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15070487/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA excel macro to transpose a specific matrix
提问by Cloud_iceberg
I'm new to VBA excel and I'm trying to code a macro that would convert a table like the following:
我是 VBA excel 的新手，我正在尝试编写一个宏来转换如下表：
Time Station1
0 150.0
60 250.0
200 450.0
250 650.0
... ...
into a transpose table like that with the values immediately following:
转换成这样的转置表，其值紧随其后：
Time 0 60 200 250 ... Station1 150.0 250.0 450.0 650.0
So far I have been using some transpose function but I need to know the size in advance.
到目前为止，我一直在使用一些转置功能，但我需要提前知道大小。
[edit]
[编辑]
I was using the following code with the table in B2:C6
我在 B2:C6 中的表格中使用了以下代码
Sub Worksheet_Change()
Set Target = ActiveCell
Application.ScreenUpdating = False
[B2:C6].Copy
[E2].PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
Target.Select
End Sub
It's more or less copying/pasting by hand..
它或多或少是手动复制/粘贴..
回答by bonCodigo
When you are using Application.WorksheetFunction.Transpose(), you don't need to know the size of the variant array. However if you are referring to the Last used rows in the two columns, then give the following code a try.
当您使用 时Application.WorksheetFunction.Transpose()，您不需要知道变体数组的大小。但是，如果您指的是两列中最后使用的行，请尝试以下代码。
In your case, you can try two variant arrays to get the multiple rows into multiple columns in one row.
在您的情况下，您可以尝试使用两个变体数组将多行放入一行中的多列。
Code:
代码：
Option Explicit
Sub rowsToColumns()
Dim vCol1 As Variant, vCol2 As Variant
Dim lastRow As Long
Dim WS As Worksheet
Set WS = Sheets(2)
lastRow = WS.Cells(WS.Rows.Count, "B").End(xlUp).Row
'--since data starting with row 2
lastRow = lastRow - 1
vCol1 = WorksheetFunction.Transpose(WS.Range("B2").Resize(lastRow).Value)
vCol2 = WorksheetFunction.Transpose(WS.Range("C2").Resize(lastRow).Value)
'-- output to sheet
WS.Range("B10").Resize(1, UBound(vCol1)) = vCol1
WS.Range("B11").Resize(1, UBound(vCol2)) = vCol2
End Sub
Sheet view:
工作表视图：
