我有一个Excel文件,其中同一列中有多个系列,我希望每个系列都进入一个单独的列中
源表:
A | B
-----------|---------
Series 1.1 | Series 3.1
Series 1.2 | Series 3.2
Series 1.3 | Series 3.3
|
|
Series 2.1 | Series 4.1
Series 2.2 | Series 4.2
Series 2.3 | Series 4.3
结果表:
A | B | C |D
-----------|------------|------------|-----------
Series 1.1 | Series 2.1 | Series 3.1 | Series 4.1
Series 1.2 | Series 2.2 | Series 3.2 | Series 4.2
Series 1.3 | Series 2.3 | Series 3.3 | Series 4.3
有什么建议?
您可以尝试下面的代码将一列中的数据拆分为多列,拆分数据基于该列上的空白单元格。(对于多列,您可以再次调用此模块)
下面的代码从Sheet1中读取单元格:Column('A'):读取500行
Public Sub break_data()
Dim row As Integer: row = 500 'Number of rows to read on a column
Dim wks As Object 'Source Sheet
Set wks = ThisWorkbook.Sheets("Sheet1")
Dim wkr As Object 'Result Sheet
Set wkr = ThisWorkbook.Sheets("Sheet2")
Dim i As Integer: i = 1
Dim j As Integer: j = 1 'Column that you want to split
Dim x As Integer: x = 1
Dim y As Integer: y = 1
Do While (i <= 500)
If (Len(Trim(wks.Cells(i, j).Value)) > 0) Then
wkr.Cells(x, y).Value = wks.Cells(i, j).Value
x = x + 1
ElseIf (Len(Trim(wks.Cells(i, j).Value)) = 0) And (x <> 1) Then
x = 1
y = y + 1
End If
i = i + 1
Loop
End Sub
资源:
结果:(运行vba之后)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句