|
- Sub 结算()
- On Error Resume Next
- Dim arr, brr(1 To 1000, 1 To 5), d, i&, s&, rng As Range
- Set d = CreateObject("scripting.dictionary")
- bh = [h3] '编号
- Range("g5:k100").ClearContents
- With Sheets("记录")
- arr = .Range("a1").CurrentRegion
- For i = 2 To UBound(arr)
- If arr(i, 2) = bh Then
- s = s + 1
- For j = 3 To 7
- brr(s, j - 2) = arr(i, j)
- Next
- If rng Is Nothing Then Set rng = .Cells(i, 2) Else Set rng = Union(rng, .Cells(i, 2))
- Else
- If Not d.exists(arr(i, 2)) Then d(arr(i, 2)) = arr(i, 2) Else d(arr(i, 2)) = d(arr(i, 2)) & "," & arr(i, 2)
- End If
- Next
- rng.EntireRow.Delete
- End With
- Range("g5").Resize(s, 5) = brr
- With Range("H3").Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:=Join(d.keys, ",")
- End With
- End Sub
复制代码 |
|