![删除空白单元格的 VBA 代码](https://linux22.com/image/1555127/%E5%88%A0%E9%99%A4%E7%A9%BA%E7%99%BD%E5%8D%95%E5%85%83%E6%A0%BC%E7%9A%84%20VBA%20%E4%BB%A3%E7%A0%81.png)
知道为什么这行不通吗?我一直收到运行时错误 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
答案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