excel - For循环不迭代VBA

Question

如果第 i 行第 2 列和第 i 行第 3 列中的值为空，我将尝试删除整行。我的 for 循环只迭代一次。知道为什么吗？

Sub DeleteEm()

RowCount = Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To RowCount
    If IsEmpty(Cells(i, 2).Value) = True And IsEmpty(Cells(i, 3).Value) = True Then
        Rows(i).EntireRow.Delete
    End If
Next i

End Sub

谢谢！

标签： excelvbafor-loop

Answer 1

删除两列中包含空单元格的行

• 您还可以考虑使用Application.Union“收集”一个范围内的行范围，然后一次性删除该范围。

编码

Option Explicit

Sub DeleteEm()

    RowCount = Cells(Rows.Count, "A").End(xlUp).Row
    
    Dim rng As Range
    For i = 2 To RowCount
        ' Considering 'blank cells = empty cells + ""-cells'.
        ' Either (For empty cells)...
        If IsEmpty(Cells(i, 2)) And IsEmpty(Cells(i, 3)) Then
        ' ...or (for blank cells)
        'If Len(Cells(i, 2)) = 0 And Len(Cells(i, 3)) = 0 Then
            If rng Is Nothing Then
                Set rng = Rows(i)
            Else
                Set rng = Union(rng, Rows(i))
            End If
        End If
    Next i
    If Not rng Is Nothing Then
        rng.Delete
    End If

End Sub