如何将来自多个Excel列的数据整合到一列中 [英] How to Consolidate Data from Multiple Excel Columns All into One Column
问题描述
获取它的最有效的方法是什么,以便将所有数据整合到一列(IE - 80,000行列A中的数据,而不是2行数据分布在4列中)。
另外,如何实现该解决方案。我的意思是,如果你的解决方案不是一个公式,而是VBA,那我该如何实现呢?
谢谢!
保存工作簿。如果这段代码没有做你想要的,返回的唯一方法是关闭而不保存并重新打开。
选择要在一列中列出的数据。必须是连续列。可能包含空白单元格。
按Alt + F11打开VBE
按Control + R查看Project Explorer
导航到您的工作簿的项目,然后选择插入 - 模块
将代码粘贴到代码中窗格
Sub MakeOneColumn()
Dim vaCells As Variant
Dim vOutput()As变量
Dim i As Long,j As Long
Dim lRow As Long
如果TypeName(Selection)=Range然后
如果Selection.Count> 1然后
如果Selection.Count< = Selection.Parent.Rows.Count然后
vaCells = Selection.Value
ReDim vOutput(1到UBound(vaCells,1)* UBound(vaCells,2),1到1)
对于j = LBound(vaCells,2)到UBound(vaCells,2)
对于i = LBound(vaCells,1) (vaCells,1)
如果Len(vaCells(i,j))> 0然后
lRow = lRow + 1
vOutput(lRow,1)= vaCells(i,j)
End If
Next i
Next j
Selection.ClearContents
Selection.Cells(1).Resize(lRow).Value = vOutput
End If
End If
End If
End Sub
按F5运行代码
Lets say I have an excel sheet with 4 columns of data & 20,000 rows of data in each column.
What is the most efficient way to get it so that I have all of that data consolidated into one column (I.E. - 80,000 rows of data in column A instead of 20,000 rows of data spread out across 4 columns).
Also, how to implement that solution. What I mean is, if your solution isn't a "formula" but VBA, how do I implement that solution?
Thanks!
Save your workbook. If this code doesn't do what you want, the only way to go back is to close without saving and reopen.
Select the data you want to list in one column. Must be contiguous columns. May contain blank cells.
Press Alt+F11 to open the VBE
Press Control+R to view the Project Explorer
Navigate to the project for your workbook and choose Insert - Module
Paste this code in the code pane
Sub MakeOneColumn()
Dim vaCells As Variant
Dim vOutput() As Variant
Dim i As Long, j As Long
Dim lRow As Long
If TypeName(Selection) = "Range" Then
If Selection.Count > 1 Then
If Selection.Count <= Selection.Parent.Rows.Count Then
vaCells = Selection.Value
ReDim vOutput(1 To UBound(vaCells, 1) * UBound(vaCells, 2), 1 To 1)
For j = LBound(vaCells, 2) To UBound(vaCells, 2)
For i = LBound(vaCells, 1) To UBound(vaCells, 1)
If Len(vaCells(i, j)) > 0 Then
lRow = lRow + 1
vOutput(lRow, 1) = vaCells(i, j)
End If
Next i
Next j
Selection.ClearContents
Selection.Cells(1).Resize(lRow).Value = vOutput
End If
End If
End If
End Sub
Press F5 to run the code
这篇关于如何将来自多个Excel列的数据整合到一列中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!