請測試看看,謝謝
Sub test()
Dim Arr, xD, Brr(1 To 1000, 1 To 2), T, n%, s%, i&, j%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Sheets(2).[a1].CurrentRegion
For j = 1 To UBound(Arr, 2)
If Arr(3, j) = "" Then GoTo 95
xD(j & "|" & Arr(3, j)) = ""
95: Next
Arr = Sheets(1).[a1].CurrentRegion
For i = 1 To UBound(Arr)
For j = 2 To UBound(Arr, 2)
T = j - 1 & "|" & Arr(i, j)
If xD.Exists(T) Then n = n + 1
Next
If n = xD.Count Then
s = s + 1: Brr(s, 1) = Arr(i, 1)
End If
n = 0
Next
xD.RemoveAll
For i = 1 To s
If i < s Then
T = Brr(i + 1, 1) - Brr(i, 1)
If xD.Exists(T) Then
Brr(xD(T), 2) = Brr(xD(T), 2) + 1
Else
n = n + 1: xD(T) = n
Brr(n, 1) = T: Brr(n, 2) = 1
End If
End If
Next
Sheets(2).[L2].Resize(n, 2) = Brr
End Sub
|