|
本帖最后由 大熊123 于 2017-7-6 19:38 编辑
Sub 多条件筛选()
Dim ar, br, d As Object, i&, j&, flag As Boolean
Set d = VBA.CreateObject("scripting.dictionary")
ar = Array("强力", "中力", "小力")
With ActiveSheet.AutoFilter.Range
br = Intersect([E:E], .Offset(0, 0))
For i = 1 To UBound(br)
For j = 0 To UBound(ar)
If InStr(br(i, 1), ar(j)) Then flag = True: Exit For
Next j
If Not flag Then
If Not d.exists(br(i, 1)) Then d(br(i, 1)) = ""
End If
flag = False
Next i
.AutoFilter Field:=5, Criteria1:=d.keys, Operator:=xlFilterValues
End With
Set d = Nothing
End Sub
尊敬的老师:上面这个VBA代码是我在论坛里抄来的,可以多条件筛选掉包含("强力", "中力", "小力")的数据,但是我想要的是筛选结果是只留下含有("强力", "中力", "小力")这几个关键字的数据行,而不是不含。请问,该怎么改代码呢?请给予帮助,我是个菜鸟不懂,谢谢!
Sub 多条件筛选()
Dim ar, br, d As Object, i&, j&, flag As Boolean
Set d = VBA.CreateObject("scripting.dictionary")
ar = Array("强力", "中力", "小力")
With ActiveSheet.AutoFilter.Range
br = Intersect([E:E], .Offset(0, 0))
For i = 1 To UBound(br)
For j = 0 To UBound(ar)
If InStr(br(i, 1), ar(j)) Then flag = True: Exit For
Next j
If flag Then'not去了就反过来了
If Not d.exists(br(i, 1)) Then d(br(i, 1)) = ""
End If
flag = False
Next i
.AutoFilter Field:=5, Criteria1:=d.keys, Operator:=xlFilterValues
End With
Set d = Nothing
End Sub
|
|