VBA实现自动匹配数据行数填充XLOOKUP公式并保留空行空白的问题求助

Hey Miki, 我来帮你搞定这个VBA的小问题！你现在遇到的情况是固定填充300行XLOOKUP公式，结果没数据的行返回0，想要这些空行保持空白对吧？给你两个实用的解决方案，按需选就行：

方案1：只给有数据的行填充公式（最推荐） #

核心思路是先找到你数据区域的最后一行有效行，只把XLOOKUP公式填充到那一行，后面的行本来就是空白，完全不用处理。这样既避免了多余的0，还能提升代码效率。

首先，你得确定哪一列是你的数据主列（比如A列，假设A列有数据的行就是你要处理的行），用VBA获取最后一行的行号，然后只给这个范围内的I列单元格填充公式。另外，尽量别用Select和Selection，VBA里直接操作单元格范围效率更高，也不容易出问题。

给你修改后的完整Sub代码：

Sub report()
    Dim ws As Worksheet
    Dim lastRow As Long
    
    ' 避免重复用Select，直接指定工作表
    Set ws = ThisWorkbook.Sheets("report1")
    
    ' 删除多余行和列
    ws.Rows("1:5").Delete Shift:=xlUp
    ws.Columns("A:B").Delete Shift:=xlToLeft
    ws.Columns("F:K").Delete Shift:=xlToLeft
    ws.Columns("I:Q").Delete Shift:=xlToLeft
    ws.Columns("I:I").ClearContents
    
    ' 找到A列最后一行有效数据（如果你的主列不是A，改成对应列号，比如"B"）
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 只给I2到I[lastRow]填充XLOOKUP公式
    If lastRow >= 2 Then ' 确保有数据行才填充
        ws.Range("I2:I" & lastRow).FormulaR1C1 = "=XLOOKUP(RC[-7],Route!C[-8],Route!C[14],"""")"
    End If
End Sub

解释下：

• 用Set ws = ...直接绑定工作表，不用反复Select，代码更稳定
• lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row是获取A列最后一个有数据的行号
• 公式里最后加了""，是让XLOOKUP在找不到匹配时返回空白（不是0），双重保险

方案2：修改XLOOKUP公式让空行返回空白（适合必须填充300行的场景） #

如果因为某些原因你必须填充300行公式，那直接修改XLOOKUP的第四个参数，指定找不到匹配或者源单元格为空时返回空字符串，而不是默认的0。

把你的公式改成这样：

ActiveCell.FormulaR1C1 = "=XLOOKUP(RC[-7],Route!C[-8],Route!C[14],"""")"

这样即使填充到300行，只要RC[-7]（也就是你要匹配的源单元格）是空的，XLOOKUP就会返回空白，不会显示0。不过还是更推荐方案1，毕竟没必要给空行加公式，浪费资源~

备注：内容来源于stack exchange，提问作者Miki