Excel按首字母筛选一列以获取2个以上的值 [英] Excel filter a column by the first letters for more than 2 values

问题描述

我在vba刚起步，现在与一个宏进行斗争，该宏将按首个精确字母过滤Column(例如，我有Column N-"City"，因此我必须拥有所有条目，以"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个值(在那种情况下，我可以在列表"begins with"中使用).我有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屋！