☰

删除空白单元格的 VBA 代码

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 vba microsoft-excel-2007
删除空白单元格的 VBA 代码

知道为什么这行不通吗？我一直收到运行时错误 1004：应用程序定义或对象定义错误。如果第一个单元格为空，我尝试删除一整行，然后如果它不为空，则删除所有空行，直到有一行包含值。这只是为了轻松清理电子表格。我已附上代码。在此处输入图片描述

Private Sub checkRows()

'H or 8
If IsEmpty(Range("A827").Value) = True Then
    Rows("825:925").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
    Rows("827:925").Select
    Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If

'G or 7
If IsEmpty(Range("A725").Value) = True Then
    Rows("723:823").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
    Rows("725:823").Select
    Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If

'F or 6
If IsEmpty(Range("A623").Value) = True Then
    Rows("621:721").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
    Rows("623:721").Select
    Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If

'E or 5
If IsEmpty(Range("A521").Value) = True Then
    Rows("519:619").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
    Rows("521:619").Select
    Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If

'D or 4
If IsEmpty(Range("A419").Value) = True Then
    Rows("417:517").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
    Rows("419:517").Select
    Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If

'C or 3
If IsEmpty(Range("A317").Value) = True Then
    Rows("315:415").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
    Rows("317:415").Select
    Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If

'B or 2
If IsEmpty(Range("A215").Value) = True Then
    Rows("213:313").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
    Rows("215:313").Select
    Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If

'A or 1
If IsEmpty(Range("A113").Value) = True Then
    Rows("111:211").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
    Rows("113:211").Select
    Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If

'RP
If IsEmpty(Range("A9").Value) = True Then
    Rows("7:107").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
    Rows("9:107").Select
    Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If

End Sub

VBA 窗口

答案1

您不能使用Rows()列引用。只需将第一部分更改为仅行即可。

If IsEmpty(Range("A827").Value) = True Then
    Rows("825:925").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
    Rows("827:925").EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If

或者你可以这样做Range("A825:A925").EntireRow.Delete ...

答案2

未使用“目标”范围参数，并且您未正确使用行方法。如果这是静态范围，那么我建议使用类似以下内容：

Private Sub RemoveRows()

  If IsEmpty(ActiveSheet.Range("A827").Value) = True Then ActiveSheet.Range("A827:A925").Clear

End Sub

如果您打算传递范围参数，我建议传递范围的字符串值而不是完整的范围对象：

Private Sub RemoveRows(ByVal TargetRange As String)

If IsEmpty(ActiveSheet.Range("A827").Value) = True Then ActiveSheet.Range(TargetRange).Clear

End Sub

然后要使用它，只需将范围的字符串表示形式作为参数传递：

Private Sub CallRemoveRows()

RemoveRows ("A827:A895")

End Sub

相关内容