使用 Excel VBA 删除特殊类型的空白单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11994187/
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
Removing SpecialType Blank Cells using Excel VBA
提问by jeff m
This script "works", but only if I run it twice. Anyone have an idea of why this is happening? Dealing with the special types has been somewhat of a nightmare, I'm not sure if that's just me or known issues. Using Excel 2010 on Windows 7. I've tried repeating the code twice as well to no avail. I tried putting this in a Do Until and ALWAYS get stuck in a forever loop the first time I execute. I'm not sure why executing it the second time seems to work
这个脚本“有效”，但前提是我运行它两次。任何人都知道为什么会发生这种情况？处理特殊类型有点像一场噩梦，我不确定这只是我的问题还是已知问题。在 Windows 7 上使用 Excel 2010。我也尝试过两次重复代码但无济于事。我试着把它放在一个直到我第一次执行时总是卡在一个永远的循环中。我不确定为什么第二次执行它似乎有效
'Remove all Blank Cells
On Error Resume Next
For i = Cells.SpecialCells(xlCellTypeBlanks).Count To 1 Step -1
Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Next i
If Cells.SpecialCells(xlCellTypeBlanks).Count = 0 Then
ActiveWorkbook.Close (True)
回答by Tim Williams
EDIT: Updated answer to show how deleting a "specialcells" range doesn't reset the sheet's UsedRangeproperty, and how that can lead to problems.
编辑：更新答案以显示删除“特殊单元格”范围不会重置工作表的UsedRange属性，以及如何导致问题。
Try running this sub multiple times on a sheet with or without the call to Activesheet.UsedRangecommented out...
尝试在工作表上多次运行此子程序，无论是否调用Activesheet.UsedRange注释掉...
Sub Tester()
Dim rng As Range
On Error Resume Next
Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
Debug.Print "have empty cells"
rng.EntireRow.Delete
ActiveSheet.UsedRange
Else
Debug.Print "no empty cells"
End If
End Sub
Saving and re-opening seems to also reset UsedRange...
保存和重新打开似乎也会重置 UsedRange...
EDIT2you should be very careful using this! It deletes whole rows even if there are non-blank cells in that row. It will be OK for certain types of data layout, but not for others. Under certain circumstances you may also see an error "Cannot use that command on overlapping selection" when calling "delete".
EDIT2你应该非常小心地使用它！即使该行中有非空白单元格，它也会删除整行。它适用于某些类型的数据布局，但不适用于其他类型。在某些情况下，您可能还会在调用“删除”时看到错误“无法在重叠选择上使用该命令”。
回答by RocketDonkey
I'll caveat this by saying that @TimWilliams's answer is more elegant, but you can try this:
我会警告说@TimWilliams 的回答更优雅，但你可以试试这个：
Sub Deletes()
Dim rng As Range
Set rng = ActiveSheet.UsedRange
' Check each cell in the range and if its value is empty, delete the row
For Each Cell In rng
If Cell.Value = "" Then
Cell.EntireRow.Delete
End If
Next Cell
' Close
ActiveWorkbook.Close
End Sub
