Excel VBA 删除表格中的空行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24659715/
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 Delete empty rows in table
提问by Nuno Nogueira
I have a table tabelaClientesin Sheet "Clientes" and I want to delete the rows where the field "Nome" is empty.
我tabelaClientes在工作表“ Clientes”中有一个表，我想删除字段“ Nome”为空的行。
How do I do that?
我怎么做？
This is what I'm trying:
这就是我正在尝试的：
Sub Cliente()
Dim ws As Worksheet
Dim row As Range
Set ws = Sheets("Clientes")
For Each row In ws.[tabelaClientes[Nome]].Rows
If row.Value = "" Then
row.Delete
End If
Next
Exit Sub
But this is deleting only some of the rows where Nomeis empty, not all, why?
但这只是删除了一些Nome空的行，而不是全部，为什么？
回答by ApplePie
You can use a very simple call to SpecialCells() to do that instead of using a loop.
您可以使用对 SpecialCells() 的非常简单的调用来执行此操作，而不是使用循环。
Range("tabelaClientes[Nome]").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Edit: To expand on my answer because I was in a hurry. SpecialCells mimic the menu that you will find in Excel after having pressed F5 and selected "Special cells... Blanks". This has the advantage of selecting all blanks at the same time and then delete the rows. Iteration can be very slow if your table is getting large thus this way will save a lot of time.
编辑：扩展我的答案，因为我很匆忙。SpecialCells 模仿按 F5 并选择“特殊单元格...空白”后在 Excel 中找到的菜单。这具有同时选择所有空白然后删除行的优点。如果您的表变大，迭代可能会非常慢，因此这种方式将节省大量时间。
It does seem that you cannot delete multiple non-contiguous rows in a table. You can do either one of two things:
似乎您无法删除table 中的多个非连续行。您可以执行以下两种操作之一：
1- Convert back the table to a range and change the reference to a standard excel reference
1- 将表格转换回一个范围并将参考更改为标准的 excel 参考
2- Loop through the results of SpecialCells().
2- 循环访问 SpecialCells() 的结果。
Option #2 will yield in slower code because of the loop but it will still be better than looping through all cells and check if they are blank but I can understand that you may need to keep it as a table.
由于循环，选项#2 将产生较慢的代码，但它仍然比循环遍历所有单元格并检查它们是否为空要好，但我可以理解您可能需要将其保留为表格。
