使用变体数组和字典对象,这样的事情非常快
该代码转储输出A1:Dx
to F1
更新:固定名称数字
Sub ReCut()
Dim X
Dim Y
Dim C
Dim lngRow As Long
Dim lngCol As Collection
Dim lngCnt1 As Long
Dim lngCnt As Long
Dim objDic As Object
Set objDic = CreateObject("scripting.dictionary")
X = Range([a1], Cells(Rows.Count, "C").End(xlUp)).Value2
Y = X
ReDim Y(1 To UBound(Y), 1 To 100)
For lngCnt1 = 1 To (UBound(Y, 2) - 3)
Y(1, lngCnt1) = "Name" & lngCnt1
Next
For lngRow = 1 To UBound(X, 1)
If objDic.exists(X(lngRow, 1) & X(lngRow, 2) & X(lngRow, 3)) Then
'find first blank entry in relevant array row
C = Split(Join(Application.Index(Y, lngCnt), "| "), "|")
Y(lngCnt, Application.Match(" ", C, 0)) = X(lngRow, 4)
Else
lngCnt = lngCnt + 1
Y(lngCnt, 1) = X(lngRow, 1)
Y(lngCnt, 2) = X(lngRow, 2)
Y(lngCnt, 3) = X(lngRow, 3)
Y(lngCnt, 4) = X(lngRow, 4)
objDic.Add X(lngRow, 1) & X(lngRow, 2) & X(lngRow, 3), lngCnt
End If
Next
[f1].Resize(UBound(Y, 1), UBound(Y, 2)) = Y
End Sub