VBA扩展列范围后报错1004：无法对重叠选区执行命令

问题背景 #

原本可正常运行的EXPORT_POR宏，在为工作表新增列后，将代码中的范围从A:C修改为A:G，运行时出现error 1004 - Cannot use that command on overlapping selections错误。

原正常代码 #

Sub EXPORT_POR()
Dim wb As Workbook
Dim ws As Worksheet
Dim FileSaveName As Variant

FileSaveName = Application.GetSaveAsFilename(fileFilter:="Text (Tab delimited) (*.*), *.*")

Sheets("POR_FINAL_OU").Copy
Set wb = ActiveWorkbook
Set ws = ActiveSheet

With wb
    With ws
        .Range("A:C").SpecialCells(xlCellTypeBlanks).EntireRow.DELETE
        .Range(.Cells(1, "C"), .Cells(.Rows.Count, .Columns.Count)).Clear
    End With
    .SaveAs FileSaveName, xlTextWindows
    .Close False
End With

i = MsgBox("Soubor uložen", vbOKOnly + vbInformation)

End Sub 

更新后报错的代码 #

Sub EXPORT_POR()
Dim wb As Workbook
Dim ws As Worksheet
Dim FileSaveName As Variant

FileSaveName = Application.GetSaveAsFilename(fileFilter:="Text (Tab delimited) (*.*), *.*")

Sheets("POR_FINAL_OU").Copy
Set wb = ActiveWorkbook
Set ws = ActiveSheet

With wb
    With ws
        .Range("A:G").SpecialCells(xlCellTypeBlanks).EntireRow.DELETE
        .Range(.Cells(1, "G"), .Cells(.Rows.Count, .Columns.Count)).Clear
    End With
    .SaveAs FileSaveName, xlTextWindows
    .Close False
End With

i = MsgBox("Soubor uložen", vbOKOnly + vbInformation)

End Sub

错误原因 #

错误出在.Range("A:G").SpecialCells(xlCellTypeBlanks).EntireRow.DELETE这行：当选择A:G列的所有空白单元格后，EntireRow会选中这些单元格所在的整行。如果同一行在A:G列中有多个空白单元格，会导致同一行被重复选中，Excel无法处理这种重叠的选择范围，从而抛出1004错误。

修复方案 #

通过单独收集需要删除的行并一次性删除，避免重复选择同一行。同时添加错误处理，防止没有空白单元格时代码报错。

修复后的代码 #

Sub EXPORT_POR()
Dim wb As Workbook
Dim ws As Worksheet
Dim FileSaveName As Variant
Dim deleteRows As Range ' 存储需要删除的行

FileSaveName = Application.GetSaveAsFilename(fileFilter:="Text (Tab delimited) (*.*), *.*")

Sheets("POR_FINAL_OU").Copy
Set wb = ActiveWorkbook
Set ws = ActiveSheet

With wb
    With ws
        ' 捕获A:G列中所有空白单元格所在的行，忽略无空白的情况
        On Error Resume Next
        Set deleteRows = .Range("A:G").SpecialCells(xlCellTypeBlanks).EntireRow
        On Error GoTo 0
        
        ' 如果存在需要删除的行，一次性删除
        If Not deleteRows Is Nothing Then
            deleteRows.Delete
        End If
        
        ' 清理G列及之后的内容
        .Range(.Cells(1, "G"), .Cells(.Rows.Count, .Columns.Count)).Clear
    End With
    .SaveAs FileSaveName, xlTextWindows
    .Close False
End With

MsgBox "Soubor uložen", vbOKOnly + vbInformation

End Sub

关键说明 #

1. 使用deleteRows变量统一存储需要删除的行，避免重复选择同一行
2. 添加On Error Resume Next和On Error GoTo 0处理无空白单元格的场景，防止代码崩溃
3. 删除了未定义的变量i，直接调用MsgBox即可

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