Remove row REF and N/A

[hannes]

I found this code to remove the row in case a cell is left blank.


How can I amend it that it removes the row in case there is a #N/A or
#REF! exception mode in the cell?


Thank you.

Sebastien

============================


Public Sub DeleteRows()
Dim rng As Range


Application.ScreenUpdating = False


On Error Resume Next
Set rng =
Worksheets("ExportData").Range*("B1:B1000").SpecialCells(xlCe*llTypeBlanks)


On Error GoTo 0


If Not rng Is Nothing Then rng.EntireRow.Delete


Worksheets("ExportData").Selec*t
Range("A1").Select


Application.ScreenUpdating = True


End Sub

[Ron Coderre]

I think this will work for you:

Sub DeleteBlankAndErrRows()
Dim rng1 As Range

Application.ScreenUpdating = False

On Error Resume Next
Set rng1 = Worksheets("ExportData").Range("B1:B1000").SpecialCells(xlCellTypeBlanks)
If Err.Number = 0 Then
rng1.EntireRow.Delete
End If

Err.Number = 0
On Error Resume Next
Set rng1 = Worksheets("ExportData").Range("B1:B1000").SpecialCells(xlCellTypeFormulas, xlErrors)
If Err.Number = 0 Then
rng1.EntireRow.Delete
End If

Set rng1 = Nothing

Application.ScreenUpdating = true

End Sub

Does that help?

Ron

[hannes]

It does not react. I put in the formula and ran the macro but nothin happens.

Could you help me out thanks!

[Ron Coderre]

I created a worksheet named "ExportData" and ran a constant downs cells B1:B1000. Then I cleared the contents of several cells and entered =na() in a few others. I also entered =A1 in a cell and copied it up one cell, causing the #REF! error.

After doing that, I ran the DeleteBlankAndErrRows macro. It found all of the errors and blank cells and deleted their rows.

Am I adequately covering the kinds of information that you have in your worksheet? What issues are you running into?

Ron

[hannes]

It only deletes rows with empty cells but not those with either a #REF! or #N/A






Sub DeleteBlankAndErrRows()
Dim rng1 As Range

Application.ScreenUpdating = False

On Error Resume Next
Set rng1 = Worksheets("Data1").Range("A1:AF65536").SpecialCells(xlCellTypeBlanks)
If Err.Number = 0 Then
rng1.EntireRow.Delete
End If

Err.Number = 0
On Error Resume Next
Set rng1 = Worksheets("Data1").Range("A1:AF65536").SpecialCells(xlCellTypeFormulas, xlErrors)
If Err.Number = 0 Then
rng1.EntireRow.Delete
End If

Set rng1 = Nothing

Application.ScreenUpdating = True

End Sub

[hannes]

I got it working


Thank you soooooo much


Sebastien