VBA执行ClearContents导致相邻单元格公式偏移或出现#REF错误如何解决

ClearContents方法本身仅会清空目标单元格的内容，不会主动修改周边单元格的公式引用，你遇到的公式自动偏移、出现#REF!错误的核心原因是Excel默认开启的自动扩展数据区域格式及公式功能被触发：当你清空同属一个数据区域的某行部分单元格时，Excel会误判你要移除该行数据，自动将下方公式上移调整，进而出现引用错位、引用丢失的问题。

你可以根据自己的使用场景任选以下一种方案：

• 方案1：执行清空操作前后临时关闭自动扩展功能
  把你的代码调整为如下形式，操作完成后再恢复默认设置，不会影响Excel的正常使用：

  ' 清空操作前关闭自动扩展
  Application.AutoCorrect.AutoExpandListRange = False
  
  ' ========== 你的原有业务代码 ==========
  Dim Sht1 As Worksheet, Sht2 As Worksheet
  Dim LstRw As Long, lookup_rng As Range, x
  Dim Sht3 As Worksheet, Sht4 As Worksheet
  Dim LstRw2 As Long, lookup_rng2 As Range, x2
  
  Set Sht1 = Sheets("Pick Ticket Schedule")
  Set Sht2 = Sheets("Pick Ticket (History)")
  Set lookup_rng = Sht2.Range("B2:B10")
  
  With Sht1
      LstRw = .Cells(.Rows.Count, "T").End(xlUp).Row
      For x = LstRw To 2 Step -1
          If Not lookup_rng.Find(what:=.Cells(x, 20), LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then
              Sht1.Range("J" & x & ":K" & x).ClearContents
              Sht1.Range("T" & x).ClearContents
          End If
      Next x
  End With
  
  Set Sht3 = Sheets("Master Production Schedule")
  Set Sht4 = Sheets("Historical FG")
  Set lookup_rng2 = Sht4.Range("B2:B10")
  
  With Sheets("Master Production Schedule")
      LstRw2 = .Cells(.Rows.Count, "V").End(xlUp).Row
      For x2 = LstRw2 To 2 Step -1
          If lookup_rng2.Find(what:=.Cells(x2, 22), LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then
              .Range("L" & x2 & ":M" & x2).ClearContents
              .Range("T" & x2).ClearContents
              .Range("V" & x2).ClearContents
          End If
      Next x2
  End With
  ' ========== 原有业务代码结束 ==========
  
  ' 操作完成后恢复默认设置
  Application.AutoCorrect.AutoExpandListRange = True
  

• 方案2：修改公式为绝对引用，锁死引用列
  把N、O列的公式的引用列前加$锁死，避免自动偏移，比如原公式=IFERROR(F2/G2*3600,0)修改为=IFERROR($F2/$G2*3600,0)即可。
• 方案3：用赋值空值替代ClearContents
  直接给目标单元格赋值空值，不会触发Excel的区域变更事件，也就不会联动调整公式：
  把代码中所有的ClearContents替换为.Value = ""即可，示例：

  ' 替换前
  '.Range("L" & x2 & ":M" & x2).ClearContents
  ' 替换后
  .Range("L" & x2 & ":M" & x2).Value = ""
  

如果你使用了Excel的结构化表（选中单元格顶部带筛选箭头的表格式布局），优先使用方案1即可解决问题。

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