vba 如何将多列转换为单列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1780235/
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
How do I convert multiple columns to a single column?
提问by zfedoran
I have a table that looks like this:
我有一张看起来像这样的表：
Col1 Col2 Col3 Col4
a b c d
e f g h
I need to convert it to this:
我需要将其转换为：
Col1 New
a b
a c
a d
e f
e g
e h
I have a ton of data and doing this by hand is out of the question. Does anyone know of a fast way to do this? I'm hoping that there is a built in way (such as paste->transpose or a function?). If not could someone point me at a relevant VBA example since I am little rusty at VBA.
我有大量数据，手动执行此操作是不可能的。有谁知道一种快速的方法来做到这一点？我希望有一种内置的方式（例如粘贴->转置或函数？）。如果没有，有人可以指出我相关的 VBA 示例，因为我对 VBA 有点生疏。
回答by Adam Ralph
Select the data you want to transform (not including column headings) and run the following macro.
选择要转换的数据（不包括列标题）并运行以下宏。
Sub Transform()
Dim targetRowNumber As Long
targetRowNumber = Selection.Rows(Selection.Rows.Count).Row + 2
Dim col1 As Variant
Dim cell As Range
Dim sourceRow As Range: For Each sourceRow In Selection.Rows
col1 = sourceRow.Cells(1).Value
For Each cell In sourceRow.Cells
If Not cell.Column = Selection.Column Then
Selection.Worksheet.Cells(targetRowNumber, 1) = col1
Selection.Worksheet.Cells(targetRowNumber, 2) = cell.Value
targetRowNumber = targetRowNumber + 1
End If
Next cell
Next sourceRow
End Sub
