VBA to delete an entire row with #N/A

[TheHack22]

Hi All,

I'm looking for some help with this line of code below. It's working fine, except for when there is NO "#N/A" in the dataset, it errors out when it cannot find #N/A to delete.
Can someone help me with this.? I think it needs something like, "If #N/A doesn't exist Sub". I don't know how this translates into VBA.

Sub DeleteNA()
Application.ScreenUpdating = False
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True
End Sub



Imran

 

[Rick Rothstein]

Try it this way...

Sub DeleteNA()
  Application.ScreenUpdating = False
  On Error Resume Next
  Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub

 

[Joe4]

You can just tell Excel to ignore any errors when running that line of code like this:

Sub DeleteNA()
    Application.ScreenUpdating = False
    On Error Resume Next
    Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

 

[TheHack22]

Try it this way...

Sub DeleteNA()
  Application.ScreenUpdating = False
  On Error Resume Next
  Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub

@Rick Rothstein

Thanks a million. This works well.

Imran

 

[TheHack22]

You can just tell Excel to ignore any errors when running that line of code like this:

Sub DeleteNA()
    Application.ScreenUpdating = False
    On Error Resume Next
    Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

@Joe4

Your note here was pretty helpful when I have these issues in the future("You can just tell Excel to ignore any errors when running that line of code like this:")

On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
I do have some lines of codes that I can use this for. But I'll take @Rick Rothstein as the solution for this post- he posted his solution first

Thanks to both of you
Imran

 

[Joe4]

@Joe4

Your note here was pretty helpful when I have these issues in the future("You can just tell Excel to ignore any errors when running that line of code like this:")

On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
I do have some lines of codes that I can use this for. But I'll take @Rick Rothstein as the solution for this post- he posted his solution first

Thanks to both of you
Imran

No problem! We basically posted the same solution, and he did beat me by a few seconds!