How to resolve error 400 in excel-vba

excel delete-row vba
Abhinn Wasav picture Abhinn Wasav · Jun 17, 2017 · Viewed 23.4k times · Source

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

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

Answer

A.S.H picture A.S.H · Jun 17, 2017

You can use On Error Resume Next, but this is not a usually recommended approach because it may mask other errors. Instead, try computing n in an error free way:

n = Application.CountIf(Sheets("GPF").Range("D9:D" & LROW), "")

yet another, still better way is to use AutoFilter:

Sub GPF_Sign()
  With Sheets("GPF").Range("D8:D200")
    .AutoFilter 1, ""
    .Offset(1).EntireRow.Delete
    .AutoFilter
  End With
End Sub

Related questions

Excel VBA: Delete entire row if cell in column A is blank (Long Dataset)

I am trying to delete all rows that have blank cells in column A in a long dataset (over 60 000 rows in excel) I have a VBA code that works great when I have less then aprox 32 000 cells: Sub DelBlankRows() Columns("…

Read More
VBA: Deleting Rows with a Specific Value

I'm trying to write a macro to delete all rows that have "True" in column A. Here is what I have so far: Sub deleteBlankRows3() Dim lastrow as Long Dim x as Long lastrow = 4650 For x=8 to x=lastrow If (…

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More