如何在VBA中为自动筛选数组添加动态单元格值实现Excel跨表筛选

Hey there! Let's figure out how to filter Sheet2's data based on those dynamic ME_Codes in Sheet1's B6 and below. Here are three practical methods you can choose from, depending on your needs:

This approach automatically grabs all non-empty ME_Codes from Sheet1 B6 onwards and applies the filter to Sheet2—no manual updates needed as the code count changes.

First, open the VBA editor by pressing Alt + F11, insert a new module, then paste this code:

Sub FilterByDynamicMECodes()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim filterRange As Range, targetRange As Range
    Dim lastRow As Long
    
    ' Set worksheet references
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    ' Get all non-empty ME_Codes starting at B6
    lastRow = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row
    If lastRow < 6 Then
        MsgBox "No ME_Codes found in Sheet1 B6 and below!", vbExclamation
        Exit Sub
    End If
    Set filterRange = ws1.Range("B6:B" & lastRow)
    
    ' Define the data range in Sheet2 (includes headers)
    Set targetRange = ws2.Range("A1").CurrentRegion
    
    ' Clear existing filter and apply new one
    targetRange.AutoFilter Field:=2, Criteria1:=GetFilterArray(filterRange), Operator:=xlFilterValues
End Sub

' Helper function to convert cell range to a filter-compatible array
Function GetFilterArray(rng As Range) As Variant
    Dim arr() As String
    Dim cell As Range
    Dim i As Integer
    
    ReDim arr(1 To rng.Cells.Count)
    i = 1
    For Each cell In rng
        arr(i) = cell.Value
        i = i + 1
    Next cell
    GetFilterArray = arr
End Function

• Run the macro whenever you need to refresh the filter, or set it to run automatically (e.g., when Sheet1 is updated) if needed.

If you prefer a point-and-click solution, use Excel's built-in Advanced Filter:

1. In Sheet1, pick a blank column (say, column D). Enter ME_Code in D1 (matching the header in Sheet2), then copy all ME_Codes from B6 down to D2 and beyond (or use =B6 in D2 and drag down to the last row of ME_Codes).
2. Switch to Sheet2, select your entire data range (including headers). Go to the Data tab and click Advanced.
3. In the Advanced Filter dialog:
   • Select "Copy to another location" if you don't want to modify the original data.
   • Set "List range" to your Sheet2 data (e.g., Sheet2!$A$1:$C$1000—make it large enough to cover future data).
   • Set "Criteria range" to the range you set up in Sheet1 (e.g., Sheet1!$D$1:$D$[last row with ME_Code]).
   • Choose a destination cell (e.g., Sheet2!E1) for the filtered results.
4. Click OK. Re-run the Advanced Filter whenever your ME_Codes update.

If you want results that refresh automatically when ME_Codes change, use a dynamic array formula (works in Excel 365/2021):

• In Sheet2, add headers in empty cells (e.g., E1=Sl_No., F1=ME_Code, G1=price).
• In E2, enter this formula:

  =FILTER(Sheet2!A:C, ISNUMBER(XMATCH(Sheet2!B:B, Sheet1!B6:B1048576)), "No matching data")
  

This will instantly pull all rows from Sheet2 where the ME_Code exists in Sheet1's B6+ range, and update automatically when ME_Codes are added/removed.

For older Excel versions (pre-365), use this array formula (enter with Ctrl + Shift + Enter):

=IFERROR(INDEX(Sheet2!$A:$C, SMALL(IF(ISNUMBER(MATCH(Sheet2!$B:$B, Sheet1!$B$6:$B$1048576, 0)), ROW(Sheet2!$A:$C)-1), ROW(A1)), COLUMN(A1)), "")

Drag this formula down and right to fill all columns and rows of results.

内容的提问来源于stack exchange，提问作者Shaumyabrata