vba 根据excel中的单元格值将单行拆分为多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26362916/
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
Split single row into multiple rows based on cell value in excel
提问by Avinash Aswani
I have a single row of data in excel. Data is around 5000+ values.
我在excel中有一行数据。数据大约有 5000 多个值。
I want to split this single row into multiple rows.Below is the example of same.
我想将此单行拆分为多行。下面是相同的示例。
My Single row contains data as follows, 1 2 3 4 5 A 1 2 4 5 9 5 9 A 2 1 4 A etc...
我的单行包含如下数据，1 2 3 4 5 A 1 2 4 5 9 5 9 A 2 1 4 A 等等...
I want this single row to be split after every "A" value it reaches. Output below.
我希望在它达到每个“A”值后拆分这一行。下面输出。
1 2 3 4 5
1 2 3 4 5
A 1 2 4 5 9 5 9
A 1 2 4 5 9 5 9
A 2 1 4
2 1 4
A Etc...
一个等...
Can some1 help me as how this can be done? Macro is fine with me. Also I have huge data like 5000+ Values.
有人可以帮助我如何做到这一点吗？宏对我来说很好。我也有大量数据，比如 5000+ 个值。
回答by Jur Pertin
This should do your job. Considering your data to be in Sheet1 and output is generated in Worksheet Sheet2.
这应该可以完成您的工作。考虑到您的数据在 Sheet1 中，并且在 Worksheet Sheet2 中生成输出。
Sub pSplitData()
Dim rngColLoop As Range
Dim rngSheet1 As Range
Dim wksSheet2 As Worksheet
Dim intColCounter As Integer
Dim intRowCounter As Integer
'Consider the data to be in First row of Sheet1
With Worksheets("Sheet1")
Set rngSheet1 = .Range(.Range("A1"), .Range("A1").End(xlToRight))
End With
Set wksSheet2 = Worksheets("Sheet2")
intRowCounter = 1
intColCounter = 0
'Clear previous output
wksSheet2.Range("A1").CurrentRegion.Clear
'Loop through and create output in Sheet2
With rngSheet1
For Each rngColLoop In .Columns
If Trim(rngColLoop) <> "" Then
If UCase(Trim(rngColLoop)) <> "A" Then
intColCounter = intColCounter + 1
wksSheet2.Cells(intRowCounter, intColCounter) = rngColLoop
ElseIf UCase(Trim(rngColLoop)) = "A" Then
intRowCounter = intRowCounter + 1
intColCounter = 1
wksSheet2.Cells(intRowCounter, intColCounter) = rngColLoop
End If
End If
Next rngColLoop
End With
Set rngColLoop = Nothing
Set rngSheet1 = Nothing
Set wksSheet2 = Nothing
End Sub
回答by user3616725
if your data is like this:
如果你的数据是这样的：
A || B || C || D ||
-----------------------------------------
1 || one || two || three || four ||
2 ||
then put =TRANSPOSE(A1:D1)in A2, and confirm with Ctrl+shift+enter, which will result in formula that looks like: {=TRANSPOSE(A1:D1)}
然后把=TRANSPOSE(A1:D1)在A2，并确认与Ctrl+ shift+ enter，这将导致公式，看起来像：{=TRANSPOSE(A1:D1)}
and a result that looks like:
结果如下：
A || B || C || D ||
-----------------------------------------
1 || one || two || three || four ||
2 || one
3 || two
4 || three
5 || four
or you you are after a one-off operation you can always: select -> copy ; select empty cell -> paste special -> tick transpose
或者您正在执行一次性操作，您可以随时：选择 -> 复制；选择空单元格 -> 选择性粘贴 -> 勾选转置
回答by ariscris
Try using Text to Columnsand use Aas your delimiter. You'll have to add the Aback into the resulting cells.
尝试使用Text to Columns和A用作分隔符。您必须将A返回添加到生成的单元格中。
回答by Matt Cremeens
If your data is in a single cell, say, cell A1, then you can use vba's split to store all of the individual pieces in an array, split with the letter A.
如果您的数据位于单个单元格中，例如 cell A1，那么您可以使用 vba 的 split 将所有单独的部分存储在一个数组中，用字母 分割A。
myArray = Split(Cells(1, 1), "A")
In your example, myArray(0) = 1 2 3 4 5, myArray(1) = 1 2 4 9 5, etc.
在您的示例中myArray(0) = 1 2 3 4 5，myArray(1) = 1 2 4 9 5, 等。
So if each of those elements needs to be in its own row, you can concatenate the Aback onto the front, like so
因此，如果这些元素中的每一个都需要在自己的行中，您可以将A背面连接到正面，就像这样
cells(2, 1) = "A" & myArray(0)
cells(3, 1) = "A" & myArray(1)
and so forth.
等等。
