具有数组的AutoFilteR [英] AutoFilteR with an array

问题描述

我正在尝试通过查看单元格是否包含这些名称之一来过滤表，并且当我使用for时，它将所有数据显示在一行中.

I'm trying to filter my table by looking if a cell contains one of those names, and when I use theFor it shows all data in one row.

这是代码:

    Dim tab(3) as string '
    'tab(0) = "*valerie dupond*"'
'tab(1) = "*emanuel babri*"'
'tab(2) = "*raphael gerand*"'

For i = 0 To 2

'Worksheets("Feuil1").Range("A1").AutoFilter field:=2, Criteria1:=tab , ''Operator:=xlFilterValues'

'Next'

推荐答案

您遇到的问题是，在数组上进行筛选时不能使用通配符(/*").

The problem that you are having is that you can't have wildcards ("/*") when you filter on an array.

​

绕开该限制很困难，但并非不可能.我以前做过的方式是做这样的事情:

Getting around that restriction is difficult, but not impossible. The way I've done it before is to do something like this:

1)将您要过滤的列(我认为第2列)中的所有值都复制到一张空白纸上.

1) Copy all of the values in the column you are filtering on (column 2, I think) to a blank sheet.

2)删除重复项.

3)循环浏览其余所有行，并删除所有不符合条件的行.

3) Loop through all of the remaining rows and delete any that do not match the criteria.

4)将剩余的值放入数组中.

4) Put the remaining values in an array.

5)过滤该阵列上的原始数据.

5) Filter the original data on that array.

我无权访问该代码，但这类似于下面的内容.我没有测试.我现在不在使用Excel的计算机上，因此您必须清理它，修复错误并在Visual Basic中启用正则表达式.应该在工具"->参考"菜单中.您也可以稍微修改一下此代码以进行操作.

I don't have access to the code, but it is something like what is below. I didn't test it. I'm not on a computer with Excel right now so you will have to clean it up, fix the errors and also enable Regular Expressions in Visual Basic. Should be in the Tools->References menu. You can also tinker with this code a bit to manipulate it to do it.

Dim i As Integer

Dim c As Integer

Dim lRow As Integer

Dim regEx As New RegExp

Dim rEx As String

Dim arr(1) As String



lRow = Range(shSheet.Rows.Count, ActiveCell.Column).End(xlup).Row 'Get's the last row of the current column selected so make sure to select the column you are trying to filter.



rEx = "^.*(valerie dupond|emanuel babri|raphael gerand).*$" ' The test string for the Regular Expression to match


'Setting up the Regular Expression.

With regEx

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = strPattern

End With



i = 0 'Sets i to be looped through your values.

c = 1 'C will be set to store the values in the array.



'Loops through every row in your table trying to match the pattern above

For i to lRow

If regEx.Test(LCase(ActiveCell.Value)) Then
arr(c) = ActiveCell.Value

c = c + 1

ReDim Preserve arr(c)

End If

ActiveCell.Offset(1,0).Select
Next i



'Sets the filter

Worksheets("Feuil1").Range("A1").AutoFilter field:=2, Criteria1:=arr , ''Operator:=xlFilterValues

方法2:

两点:

1. 您不需要FOR循环. Criteria1 = tab将过滤所有条件，不需要循环
2. 如果使用此数组方法搜索多个术语，则不能使用通配符.如果要使用通配符，则必须使用不同的语法，并且仅限于两个术语

代码2

只需删除通配符.例如，如果您只需要匹配"valerie dupond"，而不必匹配"valerie dupond夫人"

Just remove the wildcards. For example if you need to just match "valerie dupond" but not "Mrs. valerie dupond"

Sub FilterMe()
        Dim names(3) As String
        names(0) = "valerie dupond"
        names(1) = "emanuel babri"
        names(2) = "raphael gerand"
        Worksheets("Feuil1").Range("A1").AutoFilter field:=2, Criteria1:=names, Operator:=xlFilterValues
    End Sub

同样，您不能使用自动过滤器对通配符过滤两个以上的词

Again you cannot use autofilter to filter more than two terms with wildcards

这篇关于具有数组的AutoFilteR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！