删除VBA中空单元格的整行

Question

我正在尝试删除特定列具有空单元格的每一行，这些列恰好是我工作表中的第一列。我运行了这段代码：

For Y = 2 To 50000
If IsEmpty(Cells(Y, 1)) = True Then
Rows(Y).EntireRow.Delete
End If
Next Y

但是，没有任何变化。我以某种方式弄错了代码吗？

Answer 1

将第 3 行的工作表名称更改为工作表。这有一个动态循环(它将从A2循环到Column A中最后使用的行(并四舍五入要删除的行。循环完成后，它将立即删除所有行。

Option Explicit
Sub DeleteMe()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim SearchRange As Range, CurrentCell As Range, DeleteMe As Range
Set SearchRange = ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
For Each CurrentCell In SearchRange
If CurrentCell = "" Then
If DeleteMe Is Nothing Then
Set DeleteMe = CurrentCell
Else
Set DeleteMe = Union(DeleteMe, CurrentCell)
End If
End If
Next CurrentCell
If Not DeleteMe Is Nothing Then DeleteMe.EntireRow.Delete

End Sub

Answer 2

你也可以试试这个。

Sub deletecells()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim SearchRange As Range, CurrentCell As Range, DeleteMe As Range
Set SearchRange = ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
ws.Range(SearchRange.Address).SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.delete
End Sub

如果您的要求不同，请告诉我。

谢谢