|
代码你先测试一下吧,数据量不大的话速度应该没有问题
ABC列输入一次就有一次反应。- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim arr, brr, i&, j&, r&
- If Target.Column < 4 Then
- If Target.Count = 1 Then
- With Sheets(Cells(Target.Row, 1).Value)
- arr = .Range("a5:l" & .[a65536].End(3).Row)
- ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2) - 2)
- For i = 1 To UBound(arr)
- If arr(i, 1) = Cells(Target.Row, 2).Value Then
- If arr(i, 2) = Cells(Target.Row, 3).Value Then
- If Len(arr(i, 10)) + Len(arr(i, 11)) + Len(arr(i, 12)) > 0 Then
- r = r + 1
- For j = 3 To 12
- brr(r, j - 2) = arr(i, j)
- Next j
- End If
- End If
- End If
- Next i
- End With
- Cells(Target.Row, 4).Resize(UBound(brr), UBound(brr, 2)) = brr
- End If
- End If
- End Sub
复制代码 |
|