VBA 如果单元格为空，则删除行，如果没有单元格为空，则结束序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13749628/
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
VBA if cell blank, delete row, if no cells blank, end sequence
提问by kmiao91
I think I need a loop function for this but i'm not sure how to go about it.
我想我需要一个循环函数，但我不知道如何去做。
The task for this is to see if a specific column is blank, then to delete that row. But sometimes there is no blank cells and i am getting a end debug error.
这样做的任务是查看特定列是否为空，然后删除该行。但有时没有空白单元格，我收到结束调试错误。
Here is my code:
这是我的代码：
Sub DeleteRow()
Dim lr As Long
Dim shCurrentWeek As Worksheet
Set shCurrentWeek = AciveWorkbook.Sheets("Current Week")
lr = shCurrentWeek.Range("A" & Rows.Count).End(xlUp).Row
'Delete Row
shCurrentWeek.Range("B4:B" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
any ideas?
有任何想法吗？
回答by Daniel
How about just putting on error resume nextright before your Deleteline? LIke this:
直接放在on error resume next你的Delete生产线前怎么样？像这样：
On error resume next
shCurrentWeek.Range("B4:B" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
I'm assuming you don't care if it fails, so there's no need to do error trapping. This will keep an error message from displaying if there are no cells returned from your call to SpecialCells.
我假设您不在乎它是否失败，因此无需进行错误捕获。如果您的调用没有返回任何单元格，这将阻止显示错误消息SpecialCells。
回答by Scott Holtzman
While Daniel Cook is right, you could use On Error Resume Next, here is another way of going about it, since using On Error Resume Nextis really a last resort option in VBA (IMO).
虽然 Daniel Cook 是对的，但您可以使用On Error Resume Next，这是另一种方法，因为使用On Error Resume Next确实是 VBA (IMO) 中的最后手段。
The code below checks for blanks before it tries to use the SpecialCellsmethod.
下面的代码在尝试使用该SpecialCells方法之前检查空格。
Option Explicit
Sub DeleteRow()
Dim lr As Long
Dim shCurrentWeek As Worksheet
Set shCurrentWeek = ActiveWorkbook.Sheets("Current Week")
lr = shCurrentWeek.Range("A" & Rows.Count).End(xlUp).Row
If Application.WorksheetFunction.CountBlank(shCurrentWeek.Range("B4:B" & lr)) <> 0 Then
shCurrentWeek.Range("B4:B" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If
End Sub
回答by Desert Spider
Here is a piece of code I use for that type of operation.
这是我用于该类型操作的一段代码。
Sub ClearBlank()
Dim i As Long
For i = Range("B65536").End(xlUp).Row To 8 Step -1
If IsEmpty(Cells(i, 4)) Then Rows(i).DeleteNext i
End Sub
子 ClearBlank()
昏暗的我
For i = Range("B65536").End(xlUp).Row To 8 Step -1
If IsEmpty(Cells(i, 4)) Then Rows(i).Delete接下来我
结束子
I hope this helps!
我希望这有帮助！
回答by Scott
Not used VBA before today so I'm sure there is a neater way...but this code will delete any row with at least one empty cell. It assumes that you have a header row and that the first column is used for IDs so these cells are not 'looked at'.
今天之前没有使用过 VBA，所以我确定有一种更简洁的方法……但是这段代码将删除至少有一个空单元格的任何行。它假定您有一个标题行，并且第一列用于 ID，因此不会“查看”这些单元格。
The nice thing about this script is that it works for any size of spreadsheetso you don't need to hard-code values each time:
这个脚本的好处是它适用于任何大小的电子表格，因此您不需要每次都对值进行硬编码：
Sub DeleteIncompleteRows()
Dim row As Integer, col As Integer
Dim deleted As Integer
deleted = 0
Dim actualRow As Integer
Dim totalRows As Integer
Dim totalCols As Integer
totalRows = Application.CountA(Range("A:A"))
totalCols = Application.CountA(Range("1:1"))
For row = 2 To totalRows
actualRow = row - deleted
For col = 2 To totalCols
If ActiveSheet.Cells(actualRow, col).Value = "" Then
ActiveSheet.Rows(actualRow).Delete
deleted = deleted + 1
Exit For
End If
Next col
Next row
End Sub
All the best,
祝一切顺利，
Scott
斯科特
