听起来你已经搭好了基础的循环匹配逻辑，现在就差让B列的取值范围能自动跟着数据行数灵活变化对吧？下面分VBA代码优化和Excel公式动态数组两种方案来帮你实现：

一、VBA代码方案（适配任意行数） #

核心思路是先动态定位A列（键列）的最后一行数据，再以此为基准确定B列的有效范围，不管数据新增还是删减，范围都会自动同步调整。

修改后的完整代码 #

Sub GetDynamicMatchingValues()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim keyRange As Range, bValueRange As Range
    Dim resultArray() As String
    Dim targetKey As Integer
    Dim i As Long, matchCount As Long
    
    ' 替换成你的目标工作表名称
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' 设置你要匹配的目标键（这里示例设为3，可按需修改）
    targetKey = 3
    
    ' 动态获取A列最后一行有数据的行号
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 定义动态范围：从第2行开始（假设第1行是表头）到最后一行
    Set keyRange = ws.Range("A2:A" & lastRow)
    Set bValueRange = ws.Range("B2:B" & lastRow)
    
    ' 初始化结果数组，先按最大可能的匹配数分配空间
    ReDim resultArray(1 To lastRow - 1)
    matchCount = 0
    
    ' 遍历键列，匹配目标键并收集B列值
    For i = 1 To keyRange.Rows.Count
        If keyRange.Cells(i, 1).Value = targetKey Then
            matchCount = matchCount + 1
            resultArray(matchCount) = bValueRange.Cells(i, 1).Value
        End If
    Next i
    
    ' 调整数组大小到实际匹配的数量（去掉多余的空元素）
    If matchCount > 0 Then
        ReDim Preserve resultArray(1 To matchCount)
        ' 示例：将结果输出到M列，可自行修改目标位置
        ws.Range("M2").Resize(matchCount, 1).Value = Application.Transpose(resultArray)
    Else
        MsgBox "没有找到匹配的键值！"
    End If
End Sub

关键优化点 #

• 动态定位最后一行：ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 这行代码会从A列最底部往上找第一个非空单元格，精准锁定数据的最后一行，完全不用手动修改行号。
• 批量读取范围：把B列的整个有效数据范围一次性存入bValueRange，比循环逐个读取单元格效率更高，也保证了范围的动态性。
• 数组动态收缩：用ReDim Preserve把结果数组压缩到实际匹配的元素数，避免数组里出现无效空值。

二、Excel公式动态数组方案（无需VBA） #

如果你更倾向于用Excel自带的动态数组功能（适用于Excel 365/2021及以上版本），可以直接用FILTER函数搭配动态范围实现自动适配：

方法1：使用结构化表格（最推荐） #

1. 选中你的数据区域（A1:Kx），按Ctrl+T把数据转换成Excel表格（勾选“我的表格有标题”）。
2. 在任意空白单元格输入公式：

=FILTER(Table1[B列标题], Table1[A列标题]=3)

（把Table1换成你的表格名称，B列标题/A列标题换成实际的表头文字，3换成你要匹配的键）

优势：当你新增或删除数据行时，表格会自动扩展/收缩，公式的范围也会跟着自动更新，完全不用手动调整。

方法2：用函数动态计算范围 #

如果不想转成表格，也可以用COUNTA计算A列的非空行数，再用OFFSET定义动态范围：

=FILTER(OFFSET(B1,1,0,COUNTA(A:A)-1,1), OFFSET(A1,1,0,COUNTA(A:A)-1,1)=3)

（这里假设第1行是表头，COUNTA(A:A)-1就是实际的数据行数）

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