vba 如何解决excel-vba中的错误400
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44607423/
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
How to resolve error 400 in excel-vba
提问by Abhinn Wasav
I am writing an excel-vba to find and delete entire row if having blanks cell in a particular column. My macro is working fine if there is at least one blank cell but showing error 400 if there is no blank cell. My code is
如果特定列中有空白单元格，我正在编写一个 excel-vba 来查找和删除整行。如果至少有一个空白单元格，我的宏工作正常，但如果没有空白单元格，则显示错误 400。我的代码是
Sub GPF_Sign()
Dim i As Integer, n as integer
Dim LROW As Long
LROW = Sheets("GPF").Range("B200").End(xlUp).Row
n = Range("D9:D" & LROW).SpecialCells(xlCellTypeBlanks).Cells.Count
If n > 0 Then
Range("D9:D" & LROW).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If
End Sub
采纳答案by Siddharth Rout
Take your pick
随你挑
Way 1: Using OERN (On Error Resume Next)
方式 1：使用 OERN（下一个错误恢复）
Sub WAY_ONE()
Dim ws As Worksheet, LROW As Long
Dim rng As Range
Set ws = Sheets("GPF")
With ws
LROW = .Range("B" & .Rows.Count).End(xlUp).Row
On Error Resume Next
Set rng = .Range("D9:D" & LROW).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
End Sub
Way 2: Using Autofilter
方式 2：使用自动过滤器
Sub WAY_TWO()
Dim ws As Worksheet, LROW As Long
Dim rng As Range
Set ws = Sheets("GPF")
With ws
.AutoFilterMode = False
LROW = .Range("B" & .Rows.Count).End(xlUp).Row
Set rng = .Range("D9:D" & LROW)
With rng 'Filter, offset(to exclude headers) and delete visible rows
.AutoFilter Field:=1, Criteria1:=""
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub
回答by A.S.H
You can use On Error Resume Next, but this is not a usually recommended approach because it may mask other errors. Instead, try computing nin an error free way:
您可以使用On Error Resume Next，但这不是通常推荐的方法，因为它可能会掩盖其他错误。相反，尝试n以无错误的方式计算：
n = Application.CountIf(Sheets("GPF").Range("D9:D" & LROW), "")
yet another, still better way is to use AutoFilter:
另一种更好的方法是使用AutoFilter：
Sub GPF_Sign()
With Sheets("GPF").Range("D8:D200")
.AutoFilter 1, ""
.Offset(1).EntireRow.Delete
.AutoFilter
End With
End Sub
回答by Plaidpenguinhat
The method you are using ".SpecialCells(xlCellTypeBlanks)" is attempting to return a range. If there are no blank cells, the next part ".cells.count" is attempting to count Nothing. That's why it gives you an error.
您使用的方法“.SpecialCells(xlCellTypeBlanks)”试图返回一个范围。如果没有空白单元格，下一部分“.cells.count”将尝试计算 Nothing。这就是为什么它会给你一个错误。
Since you are already checking if n>0, you could just add On Error Resume Nextright above the "n= " line. If there is more code after this, you probably want to put a On Error GoTo 0after this part so it doesn't ignore later errors.
由于您已经在检查是否 n>0，您可以On Error Resume Next在“n=”行正上方添加。如果在这之后还有更多代码，您可能希望On Error GoTo 0在这部分之后放置一个，这样它就不会忽略以后的错误。
