Excel VBA - 删除表格中的空白行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12986520/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - Remove blank rows in table
提问by user91240192094
I'm trying to run a macro that selects blank cells in a table column and deletes the entire row.
我正在尝试运行一个宏，该宏选择表列中的空白单元格并删除整行。
The script below does everything except the deleting part, which prompts the following error:
下面的脚本除了删除部分，其他的都做了，提示如下错误：
run-time error 1004 - "Delete method of Range class failed"
运行时错误 1004 - “Range 类的删除方法失败”
I have used the following code:
我使用了以下代码：
Sub test()
Range("Table1[[New]]").Activate
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
End Sub
采纳答案by fthiella
Nice question! Without a table, .EntireRow.Deletealways works, but inside a table it looks like as it doesn't.
好问题！没有桌子，.EntireRow.Delete总是可以工作，但在桌子里面，它看起来好像没有。
This works:
这有效：
Sub Test()
Dim Rng As Range
On Error Resume Next
Set Rng = Range("Table1[[New]]").SpecialCells(xlCellTypeBlanks)
On Error Goto 0
If Not Rng Is Nothing Then
Rng.Delete Shift:=xlUp
End If
End Sub
回答by Doug Glancy
You actually can do it in one pass, but need to use the ListObjectobject and its DataBodyRangeand ListColumnsproperties:
实际上，你可以做到这一点的一个传球，但需要使用ListObject对象和它DataBodyRange和ListColumns属性：
Sub ClearBlankCellsInColumnNew()
Dim rngBlanks As Excel.Range
With Worksheets("Sheet1").ListObjects("Table1")
On Error Resume Next
Set rngBlanks = Intersect(.DataBodyRange, .ListColumns("New").Range).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rngBlanks Is Nothing Then
rngBlanks.Delete
End If
End With
End Sub
回答by Doug Glancy
Step 1: Make a helper column in the table where you check for any blank fields in that row. For example, if you had 3 columns in your table: A (Price), B (Quantity), and C (Cost), you would add a fourth column D and label it "Any Blanks?". The equation would be =IF(OR(ISBLANK([@Price]),ISBLANK([@Quantity]),ISBLANK([@Cost])),"Yes","No")
步骤 1：在表格中创建一个辅助列，您可以在其中检查该行中的任何空白字段。例如，如果您的表中有 3 列：A（价格）、B（数量）和 C（成本），您将添加第四列 D 并将其标记为“任何空白？”。等式是=IF(OR(ISBLANK([@Price]),ISBLANK([@Quantity]),ISBLANK([@Cost])),"Yes","No")
That would give you a column to filter to view all the blanks.
这将为您提供一列来过滤以查看所有空白。
Step 2: In VBA you would then do the following:
第 2 步：在 VBA 中，您将执行以下操作：
Range("MyTableNameHere").AutoFilter Field:=Range("MyTableNameHere[Any Blanks?]").Column, Criteria1:="Yes"
Application.DisplayAlerts = False
Range("MyTableNameHere").ListObject.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True
Range("MyTableNameHere").AutoFilter Field:=Range("MyTableNameHere[Any Blanks?]").Column
This essentially is filtering to the rows you want to delete in the table using the helper column, selecting all the visible data in the table, and unfiltering the table. I was searching around how to delete all visible rows in a table and found this and fiddled around until I figured out that this would work. Taking that and combining it with a helper column to select all rows with any blanks seems like what you were wanting as well.
这实质上是使用辅助列过滤到表中要删除的行，选择表中的所有可见数据，并取消过滤表。我一直在搜索如何删除表中所有可见的行，并找到了这个并摆弄，直到我发现这行得通。将其与辅助列结合以选择所有带有任何空白的行似乎也是您想要的。
回答by rnsousa
Adapting previous answers:
改编以前的答案：
On Error Resume Next
Set Rng = ListObjects(1).DataBodyRange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not Rng Is Nothing Then
Rng.Delete Shift:=xlUp
End If
回答by Frej Lindstr?m
Using ListObjects in Excel makes it easy to use the following to remove blank rows.
在 Excel 中使用 ListObjects 可以轻松使用以下内容删除空白行。
Sub RemoveBlankRow(ByVal SheetName As String, TableName As String)
Dim rng As Integer
rng = Sheets(SheetName).ListObjects(TableName).DataBodyRange.Rows.Count
For i = 1 To rng
If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).EntireRow.Delete
Next
End Sub
回答by Rebecca
Two notes regarding Frej Lindstrom's solution, which I used but had to tweak a little:
关于 Frej Lindstrom 的解决方案的两个注意事项，我使用过但不得不稍微调整一下：
(1) add an End If before the Next
(2) add "i = i - 1" just before the End If
(1) 在 Next 之前添加 End If
(2) 在 End If 之前添加“i = i - 1”
Why?because if you have blank rows one above each other, you'll skip one since all the rows' numbers just shifted one up. Essentially, if you deleted row [N], another row is now row [N], and you need to also test it rather than moving immediately to row [N + 1].
Why?因为如果你有一个空行，你会跳过一个，因为所有行的数字都向上移动了一个。本质上，如果您删除了row [N]，则现在是另一行row [N]，您还需要对其进行测试，而不是立即移动到row [N + 1]。
BIG CAVEAT: if your last row is blank, this will give you a stuck loop. I'll probably put in an IF to handle it, though.
大警告：如果你的最后一行是空白的，这会给你一个卡住的循环。不过，我可能会输入一个 IF 来处理它。
I know this is an old thread, but thought I'd add this in case anyone else comes through looking for similar solutions. Thank you to Frej - your code really helped!
我知道这是一个旧线程，但我想我会添加这个以防其他人通过寻找类似的解决方案来。感谢 Frej - 你的代码真的很有帮助！
