- Sub test()
- Dim i, k, m, d, num, arr
- Set d = CreateObject("Scripting.Dictionary")
- With Sheets("Sheet1")
- num = .[B2].Value
- arr = .UsedRange
- For i = 8 To UBound(arr)
- If InStr(arr(i, 2), num) > 0 Then
- If .Range(Cells(i, 1), Cells(i, UBound(arr, 2))).Interior.Color = vbYellow Then
- MsgBox "已查找过!"
- Exit Sub
- Else
- .Range(Cells(i, 1), Cells(i, UBound(arr, 2))).Interior.Color = vbYellow
- End If
-
- m = 1
- d(arr(i, 2)) = arr(i, 2)
- End If
- Next
- k = d.Keys
- If m = 1 Then
- .Range(Cells(7, 1), Cells(UBound(arr, 1) + 1, UBound(arr, 2))).AutoFilter 2, k, Operator:=xlFilterValues '对查到的值进行筛选
- Else
- MsgBox "没有找到!"
- End If
- End With
- End Sub
- Sub DelFilter()
- Dim arr
- With Sheets("Sheet1")
- arr = .UsedRange
- .Range(Cells(7, 1), Cells(UBound(arr, 1) + 1, UBound(arr, 2))).AutoFilter '取消筛选
- End With
- End Sub
复制代码
|