Excel 按第一个字母过滤一列超过 2 个值 [英] Excel filter a column by the first letters for more than 2 values

问题描述

我是 vba 的新手，现在使用一个宏来过滤列，该宏将按第一个确切的字母过滤列(例如，我有一个列 N - 城市"，因此我必须拥有所有条目，例如开头 - Vancouver"、Vancouver. BC"、Vancouver Canada" - 所以我想按照第一个字母对这个列进行排序 - VANCOU - 以确保我不会错过任何信息.

I am very new at vba, and now fighting with one macro which will filter a Column by the first exact letters (for instance, I have a Column N - "City" and as a result I have to have all entries , starts for instance- "Vancouver", "Vancouver. BC", "Vancouver Canada" – so I want to sort this column by the first letters – VANCOU - to be sure, that I will not miss any info.

下面的代码对于 3 个值根本不起作用 - 可能我选择了错误的方式.，你能告诉我 - 在这种情况下哪个函数或运算符可以工作?我找到的所有值 - 为 2 个值工作(在这种情况下，我可以使用列表以"开头).我有 5-6 个值，它们可能会有所不同(我不知道下次我会使用哪种格式的城市名称).

The code below does not work at all for 3 values – probably I choose a wrong way ., can you please advise – which function or operator will work at this case? All I find - work for 2 values (at that case I can use at list "begins with"). I have 5-6 values, and they might vary (I don't know which format of City name I will have next time) .

提前致谢！

Dim rng01 As Range
Set rng01 = [A1:Z5048]
    rng01.Parent.AutoFilterMode = False
    rng01.Columns(14).AutoFilter Field:=1, Criteria1:=Array("Vancou*", "Brampt*", "Halifa*"), Operator:= _
        xlFilterValues

更新:这是一个改编的代码，它不起作用

Upd: Here is an adapted code , which is not working

Option Explicit
Sub AutoFilterWorkaround()

Dim sht As Worksheet
Dim filterarr As Variant, tofindarr As Variant
Dim lastrow As Long, i As Long, j As Long, k As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")
lastrow = sht.Cells(sht.Rows.Count, "N").End(xlUp).Row

'List the parts of the words you need to find here
tofindarr = Array("Vancou", "Brampt", "Halifa")

ReDim filterarr(0 To 0)
j = 0

For k = 0 To UBound(tofindarr)

    For i = 2 To lastrow
        If InStr(sht.Cells(i, 14).Value, tofindarr(k)) > 0 Then
            filterarr(j) = sht.Cells(i, 14).Value
            j = j + 1
            ReDim Preserve filterarr(0 To j)
        End If
    Next i

Next k

'Filter on array
sht.Range("$N$1:$N$" & lastrow).AutoFilter Field:=14, Criteria1:=Array(filterarr), Operator:=xlFilterValues

End Sub

推荐答案

好的，所以我重写了解决方法 - 基本上我们通过查找每个单独的匹配案例，将其加载到数组中，然后过滤整个数组来避免使用通配符最后.

Okay, so I rewrote the workaround - basically we avoid using wildcards by just finding each individual match case, loading that into an array, then filter on the entire array at the end.

此示例适用于 A 列 - 只需将 lastrow 中的 A 更改为 N，并将最后一行中的 As 更改为 Ns.还要在 Set sht 行中指定您的工作表名称.此外，在您的情况下，N 列的 Field:=1 需要更改为 Field:=14.

This example works for column A - just change the A in lastrow to N, as well as changing the As to Ns in the last line. Also specify your sheet name on the Set sht line. Also Field:=1 needs to be changed to Field:=14 for column N in your case.

Option Explicit
Sub AutoFilterWorkaround()

Dim sht As Worksheet
Dim filterarr As Variant, tofindarr As Variant
Dim lastrow As Long, i As Long, j As Long, k As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")
lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

'List the parts of the words you need to find here
tofindarr = Array("Vancou", "Brampt", "Halifa")

ReDim filterarr(0 To 0)
j = 0

For k = 0 To UBound(tofindarr)

    For i = 2 To lastrow
        If InStr(sht.Cells(i, 1).Value, tofindarr(k)) > 0 Then
            filterarr(j) = sht.Cells(i, 1).Value
            j = j + 1
            ReDim Preserve filterarr(0 To j)
        End If
    Next i

Next k

'Filter on array
sht.Range("$A$1:$A$" & lastrow).AutoFilter Field:=1, Criteria1:=Array(filterarr), Operator:=xlFilterValues

End Sub

这篇关于Excel 按第一个字母过滤一列超过 2 个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！