vba 使用VBA删除Excel中的空行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7876340/
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
Deleting Empty rows in Excel using VBA
提问by user569125
I am trying to delete Empty rows by using below code:
我正在尝试使用以下代码删除空行：
worksheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
The code above is working fine, but giving run time error '1004': No Cells were found.
上面的代码工作正常，但给 run time error '1004': No Cells were found.
回答by Jon49
On Error Resume Next
worksheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
The error handling helps when there are no blank cells. SpecialCells(xlCellTypeBlanks)will always return an error if there are no cells like that so error handling is the only way (that I know of) to deal with it if you want to use SpecialCells(xlCellTypeBlanks).
当没有空白单元格时，错误处理会有所帮助。SpecialCells(xlCellTypeBlanks)如果没有这样的单元格，将始终返回错误，因此如果您想使用SpecialCells(xlCellTypeBlanks).
回答by Doug Glancy
You need to test that there are any blanks.
您需要测试是否有任何空白。
If WorksheetFunction.CountBlank(Worksheet.Columns("A:A")) > 0 Then
Worksheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If
You can just use On Error Resume Next to skip over the line if there are no blanks, but it's generally preferable to test for a specific condition, rather than assuming you know what the error will be.
如果没有空格，您可以使用 On Error Resume Next 跳过该行，但通常最好测试特定条件，而不是假设您知道错误将是什么。
As far as I can see you'd only get the "No Cells Found" message if every cell in Column A has a value.
据我所知，如果 A 列中的每个单元格都有一个值，您只会收到“未找到单元格”消息。
EDIT: Based on @brettdj's comments, here's an alternative that still uses CountBlank:
编辑：基于@brettdj 的评论，这里有一个仍然使用 CountBlank 的替代方法：
If WorksheetFunction.CountBlank(Intersect(worksheet.UsedRange, ws.Columns("A:A"))) > 0 Then
worksheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If
Of course UsedRange is notoriously fickle and may be bigger than it appears. I think it's best to first determine the actual range where the rows are to be deleted and then check the SpecialCells in that range, e.g.:
当然，UsedRange 是出了名的善变，而且可能比看起来的要大。我认为最好首先确定要删除行的实际范围，然后检查该范围内的 SpecialCells，例如：
Sub DeleteRows()
Dim ws As Excel.Worksheet
Dim LastRow As Long
Set ws = ActiveSheet
LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
With ws.Range("A2:A" & LastRow)
If WorksheetFunction.CountBlank(.Cells) > 0 Then
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If
End With
End Sub
One last note - I changed the variable from "worksheet" to "ws" as "worksheet" is an Excel reserved word.
最后一个注意事项 - 我将变量从“工作表”更改为“ws”，因为“工作表”是 Excel 保留字。
回答by niko
Working fine with me . These statement does not throw any error to me
和我一起工作很好。这些声明不会向我抛出任何错误
Sheet1.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete 'perfect
I found these type of solution for your question
我为您的问题找到了这些类型的解决方案
On Error Resume Next
Sheet1.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Take a look at these links
看看这些链接
http://www.mrexcel.com/forum/showthread.php?t=343744
http://www.mrexcel.com/forum/showthread.php?t=343744
and well yes did you set your object ? worksheet does not make any sense here
是的，您是否设置了对象？工作表在这里没有任何意义
dim wsheet as worksheets
set wsheet = worksheets("worksheetname") or worksheets("sheet1")
wsheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
回答by DanF
Another way:
其它的办法：
If Range("Table2").Rows.Count > 1 Then
Range("Table2").EntireRow.Delete
End If
回答by DanF
Sub delete_rows_blank()
t = 1
lastrow = ActiveSheet.UsedRange.Rows.Count
Do Until t = lastrow
If Cells(t, "A") = "" Then
Rows(t).Delete
End If
t = t + 1
Loop
End Sub
