如果特定范围内的单元格全部为空，则删除整行 [英] Delete entire row if cells in specific range are all empty

问题描述

我想创建一个宏，如果特定范围内的所有单元格(10行中的每行B到K)都为空，则将删除整行.我尝试了以下方法:

I want to create a macro that would delete an entire row if all the cells in specific range (B to K in each of the 10 rows) are all empty. I tried the following:

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

如果下面的行中有更多的空单元格，例如1和2，它将删除第1行并移动第2行，而不是删除已删除的行，因此它成为第1行.然后它会跳过移动的行，因为我总是增加因此它再也不会检查第1行.在移到下一个i之前，是否有任何方法可以检查刚刚删除的行是否真的是非空的?

If there are more following lines with empty cells, let's say 1 and 2, it deletes row 1 and move row 2 instead of the deleted one, so it becomes row 1. Then it skips the moved row, because i always increases so it never checks row 1 again. Is there any way to check if the row that was just deleted is really non-empty before moving to the next i?

顺便说一句:我正在使用Excel 2013.

Btw: I am using Excel 2013.

推荐答案

为什么不使用特殊单元格，而只使用 CountA()?

Instead of using the Special Cells, why not just use 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 到开头.

That way, you can avoid any errors in case there's no blanks. Also, when deleting rows, it's recommended to start at the end, then Step -1 to the beginning.

也可以尝试一下，它可能会更快:

Try this as well, it may be quicker:

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

我建议先使用F8逐步解决该问题，以了解其工作原理.在 B1:K10 范围内，它将首先选择所有包含空白单元格的行.然后，它将删除这些行.

I recommend stepping through that with F8 first, to see how it works. In the range B1:K10, it will first select all rows where there's a blank cell. Then, it'll delete those rows.

这篇关于如果特定范围内的单元格全部为空，则删除整行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！