如果特定范围内的单元格全部为空，则删除整行

Question

Dim i As Integer
Dim rng As Range

For i = 1 To 10
Set rng = Range("B" & i, "K" & i).SpecialCells(xlCellTypeBlanks)
rng.EntireRow.Delete
Next i

Answer 1

与其使用特殊单元格，为什么不直接使用counta()呢？

Sub t()
Dim i&

For i = 10 To 1 Step -1
    If WorksheetFunction.CountA(Range("B" & i, "K" & i)) = 0 Then
        Rows(i).EntireRow.Delete
    End If
Next i
End Sub

这样，在没有空白的情况下可以避免任何错误。此外，删除行时，建议从末尾开始，然后步骤-1从头开始。

编辑：

Sub T()
Dim rng As Range

Set rng = Range("B1:K10") ' Since you're going to loop through row 1 to 10, just grab the blank cells from the start
rng.SpecialCells(xlCellTypeBlanks).EntireRow.Select ' Just to show you what's being selected. Comment this out in the final code
rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub