Excel VBA自动筛选：如何实现词根类关键词的非精确匹配？

Got it, let's tackle that root word matching issue in your VBA AutoFilter. The problem with your current code is that while *circuit* technically should catch circuits (since circuits contains the substring circuit), there might be hidden issues like case sensitivity or unexpected characters in your data. Either way, let's build a more robust solution to match all variants of the "circuit" root.

Method 1: Simple Multi-Wildcard Filter (Quick Fix) #

If you just need to target common variants like circuit, circuits, or circuitry, expand your filter criteria to include all these substrings using an array:

Sub Enviardatosfiltrados()
    Dim wbLibroActual As Workbook
    Dim wsHojaActual As Worksheet
    Dim RangoDatos As Range
    Dim uFila As Long
    Dim wbLibroNuevo As Workbook
    
    Set wbLibroActual = Workbooks(ThisWorkbook.Name)
    Set wsHojaActual = wbLibroActual.ActiveSheet
    Set RangoDatos = wsHojaActual.UsedRange
    
    ' Filter for multiple root variants
    RangoDatos.AutoFilter Field:=22, _
        Criteria1:=Array("*circuit*", "*circuits*", "*circuitry*"), _
        Operator:=xlFilterValues
        
    uFila = wsHojaActual.Range("A" & Rows.Count).End(xlUp).Row
    
    ' Add code here to copy filtered rows to new workbook
    ' Example:
    Set wbLibroNuevo = Workbooks.Add
    wsHojaActual.Range("A1:V" & uFila).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=wbLibroNuevo.Sheets(1).Range("A1")
End Sub

Just add any additional variants (like circuitous) to the array if needed.

Method 2: Regex-Based Filtering (Advanced Root Matching) #

For flexible matching of any word starting with the "circuit" root (regardless of suffix), use VBA's regular expression engine. This handles all possible variants and ignores case sensitivity:

Sub Enviardatosfiltrados_Regex()
    Dim wbLibroActual As Workbook
    Dim wsHojaActual As Worksheet
    Dim RangoDatos As Range
    Dim wbLibroNuevo As Workbook
    Dim regex As Object
    Dim cell As Range
    Dim destSheet As Worksheet
    Dim destRow As Long
    
    ' Set up source workbook/sheet
    Set wbLibroActual = ThisWorkbook
    Set wsHojaActual = wbLibroActual.ActiveSheet
    Set RangoDatos = wsHojaActual.UsedRange
    
    ' Initialize regex for root word matching
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "\bcircuit\w*" ' Matches "circuit" + any word characters (letters/numbers/underscores)
    regex.IgnoreCase = True ' Ignore case differences (Circuit, CIRCUITS, etc.)
    
    ' Create new workbook for results
    Set wbLibroNuevo = Workbooks.Add
    Set destSheet = wbLibroNuevo.Sheets(1)
    destRow = 1
    
    ' Copy header row first
    RangoDatos.Rows(1).Copy destSheet.Cells(destRow, 1)
    destRow = destRow + 1
    
    ' Loop through column 22 (V) and copy matching rows
    For Each cell In wsHojaActual.Range("V2:V" & wsHojaActual.Range("A" & Rows.Count).End(xlUp).Row)
        If regex.Test(cell.Value) Then
            wsHojaActual.Rows(cell.Row).Copy destSheet.Cells(destRow, 1)
            destRow = destRow + 1
        End If
    Next cell
    
    ' Clean up objects
    Set regex = Nothing
    Set wbLibroActual = Nothing
    Set wsHojaActual = Nothing
    Set wbLibroNuevo = Nothing
End Sub

The regex pattern \bcircuit\w* ensures we only match full words starting with "circuit" (the \b is a word boundary, so it won't accidentally match something like circuittest).

Quick Note on Your Original Issue #

If *circuit* wasn't catching circuits, double-check your data for hidden spaces or unusual characters (like non-breaking spaces) around the word. The regex method will handle these cases better since it targets the actual root word structure.

内容的提问来源于stack exchange，提问作者Carolina Montoya Pachongo