Excel - 将多列合并为一列 [英] Excel - Combine multiple columns into one column

问题描述

我有多个列表，它们位于 excel 的不同列中.我需要做的是将这些数据列组合成一个大列.我不在乎是否有重复的条目，但是我希望它跳过每列的第 1 行.

I have multiple lists that are in separate columns in excel. What I need to do is combine these columns of data into one big column. I do not care if there are duplicate entries, however I want it to skip row 1 of each column.

另外如果ROW1有1月到12月的header，列的长度不一样，需要合并成一个大列怎么办?

Also what about if ROW1 has headers from January to December, and the length of the columns are different and needs to be combine into one big column?

ROW1| 1   2   3    
ROW2| A   D   G    
ROW3| B   E   H    
ROW4| C   F   I

应该合并成

A    
B    
C    
D    
E    
F    
G    
H    
I

每列的第一行需要跳过.

The first row of each column needs to be skipped.

推荐答案

试试这个.单击数据范围内的任意位置，然后使用此宏:

Try this. Click anywhere in your range of data and then use this macro:

Sub CombineColumns()
Dim rng As Range
Dim iCol As Integer
Dim lastCell As Integer

Set rng = ActiveCell.CurrentRegion
lastCell = rng.Columns(1).Rows.Count + 1

For iCol = 2 To rng.Columns.Count
    Range(Cells(1, iCol), Cells(rng.Columns(iCol).Rows.Count, iCol)).Cut
    ActiveSheet.Paste Destination:=Cells(lastCell, 1)
    lastCell = lastCell + rng.Columns(iCol).Rows.Count
Next iCol
End Sub

这篇关于Excel - 将多列合并为一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！