ALT+F11打开VBE编辑器,新建个模块,粘贴如下代码:
Sub aa()
Dim flag As Boolean, rg As Range, l As Integer, m As Integer, r As Integer, i As Integer
flag = True
m = 1
r = ActiveSheet.UsedRange.Range("a65536").End(xlUp).Row
For i = 1 To r
If Cells(i, 1) = Cells(i + 1, 1) Then
If flag Then
Set rg = Cells(i, 2)
flag = False
Else
Set rg = Union(rg, Cells(i, 2))
End If
Else
Set rg = Union(rg, Cells(i, 2))
flag = True
rg.Copy
Cells(m, 4).PasteSpecial Transpose:=True
Cells(m, 3) = Cells(i, 1)
m = m + 1
End If
Next
Columns("A:B").Delete
l = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious).Column
Debug.Print l
For i = 3 To l * 2
Columns(i).Insert
i = i + 1
Next
End Sub
点运行,即可。