|
Sub heb()
Dim x, mrg As Range
Application.DisplayAlerts = False
Set mrg = [a2]
For x = 2 To Range("a65536").End(xlUp).Row + 1
If Cells(x, 1) = Cells(x - 1, 1) Then
Set mrg = Union(mrg, Cells(x, 1))
Else
mrg.Merge
Set mrg = Cells(x, 1)
End If
Next x
Application.DisplayAlerts = True
End Sub
Sub tianc()
Dim x As Integer
Dim mrg As Range
Set mrg = [a2]
For x = 2 To Range("a65536").End(xlUp).Row - 1
If Cells(x + 1, 1) = "" Then
Cells(x + 1, 1) = mrg
Else
Set mrg = Cells(x, 1)
End If
Next x
End Sub
Sub test()
Call tianc
Dim arr, i As Long, dic, brr()
Set dic = CreateObject("Scripting.Dictionary")
arr = Range("A2:B" & Cells(Cells.Rows.Count, "A").End(xlUp).Row)
ReDim brr(1 To UBound(arr))
For i = LBound(arr) To UBound(arr)
If dic(arr(i, 1)) = "" Then
dic(arr(i, 1)) = i
brr(i) = arr(i, 2)
Else
brr(dic(arr(i, 1))) = brr(dic(arr(i, 1))) & "," & arr(i, 2)
End If
Next i
[C2].Resize(UBound(arr), 1) = WorksheetFunction.Transpose(brr)
Call heb
End Sub
|
|