Excel VBA脚本优化与INDEX/MATCH公式忽略空白需求

Q1 解决方案 #

问题出在Set r = ws.Range("A1").CurrentRegion.Offset(1)这行代码——因为A列有填充到1000行的公式，CurrentRegion会把所有带公式的行都包含进去。你需要改成以C列最后一个有数据的行来界定复制范围，修改步骤如下：

1. 先声明一个变量存储C列最后有数据的行号，在Dim r As Range后面添加：

   Dim lastRow As Long
   

2. 在Set tracker = ThisWorkbook.Sheets("TRACKER")之后，添加一行获取C列最后行号：

   lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
   

3. 替换原来的Set r = ...代码，改成基于C列最后行的动态范围：

   ' 从A2开始，到C列最后行，覆盖CurrentRegion的所有列（保留原有列范围）
   Set r = ws.Range("A2", ws.Cells(lastRow, ws.Range("A1").CurrentRegion.Columns.Count))
   

   如果你明确知道要复制到J列，也可以直接写Set r = ws.Range("A2:J" & lastRow)，前者更灵活，适合后续列数变化的情况。

这样修改后，只会复制C列有数据的行，不会带那些只有公式的空白行。

Q2 解决方案 #

你可以给MATCH添加非空条件，让它只在Sheet1的A列非空单元格中查找匹配值，修改后的公式如下：

=IFERROR(INDEX(Sheet1!G:G,MATCH(1,(Sheet1!A:A=CONCATENATE(C2))*(Sheet1!A:A<>""),0)),"Not Found")

说明：

• (Sheet1!A:A=CONCATENATE(C2))：匹配和C2拼接值相同的单元格
• *(Sheet1!A:A<>"")：添加非空判断，排除Sheet1中A列的空白单元格
• 整个条件用MATCH(1, ... ,0)来查找同时满足两个条件的第一个位置
• 如果你使用的是Excel 365/2021及以上版本，直接输入公式即可；旧版Excel需要按Ctrl+Shift+Enter作为数组公式确认执行。

这样修改后，公式会自动忽略Sheet1中A列的空白单元格，不会出现错误匹配的情况。

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