问题:有多个2列组成的多列数据,要整合成一个2列的数据,如图
步骤:
1.先把原始列中所有的空值用文字代替,此为方便VB代码的运行:按Ctrl+G--定位条件--空值--确定--输入文字,按Ctrl+Enter,会自动填充全部空值
2.运行VBA代码:
Sub Combine()
Dim OrigA
Dim OrigB
Dim strA As String
Dim strB As String
Dim strDelim As String
Dim lngCol As Long
strDelim = "||"
strA = Join(Application.Transpose(Range([a1], Cells(Rows.Count, "A").End(xlUp))), strDelim)
strB = Join(Application.Transpose(Range([b1], Cells(Rows.Count, "b").End(xlUp))), strDelim)
For lngCol = Columns("C").Column To Columns("ALC").Column - 2 Step 2
If Application.CountA(Columns(lngCol)) > 1 Then
'handle odd column range
strA = strA & (strDelim & Join(Application.Transpose(Range(Cells(1, lngCol), Cells(Rows.Count, lngCol).End(xlUp))), strDelim))
Else
'handle odd column single cell
If Len(Cells(1, lngCol)) > 0 Then strA = strA & (strDelim & Cells(1, lngCol).Value)
End If
If Application.CountA(Columns(lngCol + 1)) > 1 Then
'handle even column range
strB = strB & (strDelim & Join(Application.Transpose(Range(Cells(1, lngCol + 1), Cells(Rows.Count, lngCol + 1).End(xlUp))), strDelim))
Else
'handle even column single cell
If Len(Cells(1, lngCol + 1)) > 0 Then strB = strB & (strDelim & Cells(1, lngCol + 1).Value)
End If
Next
OrigA = Application.Transpose(Split(strA, strDelim))
OrigB = Application.Transpose(Split(strB, strDelim))
[a1].Resize(UBound(OrigA, 1), 1) = OrigA
[b1].Resize(UBound(OrigB, 1), 1) = OrigB
End Sub
3.运行结果会在A,B列追加
4.整理运行结果:把“此为空值”重新替换为空值,整理成需要的样子