|
try this:
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Not Target.Address = "$B$1" Then Exit Sub
- Dim ar As Variant, d As Object, k, t, b(), c
- c = Array(0, 1, 3, 4, 5, 6, 7, 8, 9, 10)
- Set d = CreateObject("scripting.dictionary")
- k = Sheets(3).[d1].Value
- ar = Sheets(1).[a1].CurrentRegion.Value
- For i = 2 To UBound(ar)
- If Not d.exists(ar(i, 2)) Then
- d(ar(i, 2)) = "|" & i
- Else
- d(ar(i, 2)) = d(ar(i, 2)) & "|" & i
- End If
- Next
- t = Split(d(k), "|")
- ReDim b(1 To UBound(t), 1 To 9)
- For i = 1 To UBound(t)
- For j = 1 To UBound(c)
- b(i, j) = ar(t(i), c(j))
- Next
- Next
- [a4].Resize(UBound(ar), 9).ClearContents
- [a4].Resize(UBound(b), 9) = b
- End Sub
复制代码 |
|