VBA实现多行数据合并为一列
今天收到一个水位流量关系曲线表,为了方便人工查表,这表格是一个水位级别为一行的方式排列的,也就是1分米分为10列进行填充。现在需要把数据导入到水文测报系统中,而导入的格式是要求流量以一列进行排列的,几百个数据就算用复制粘贴的方式也够整理的,而且有好几张表格,因此使用VBA实现多行数据合并为一列的功能,这一功能在日常数据处理工作也经常会用到,水文工具集提供的这一VBA函数为MultiRowsToOneColumn,具体源代码如下:
Option Explicit '================================ ' 多行数据合并为一列 ' MultiRowsToOneColumn ' '================================ Sub MultiRowsToOneColumn() Dim shtNew As Worksheet Dim rngSelection As Range Dim rngDest As Range Dim i As Integer Dim j As Integer Dim iPosOfRow As Integer Set rngSelection = Selection Set shtNew = Sheets.Add Set rngDest = shtNew.Cells(1, 1) iPosOfRow = 0 For i = 1 To rngSelection.Rows.Count For j = 1 To rngSelection.Columns.Count rngDest.Offset(iPosOfRow, 0).Value = rngSelection.Cells(i, j).Value iPosOfRow = iPosOfRow + 1 Next Next End Sub
如果需要不同选择区域进行合并的话,可以对每个区域分别合并为一列,然后把这些列串接起来即可。