VBA to delete entire colum in the range if any colum is having #N/A

[hanim554]

Dear Friends,


I trying to figure out a VBA code to delete entire row in the range O6:U500 if any of the colum in the range is having the value #N/A. (nothing to happen to the data in the rest of the column). Could any please help me.


Regards

Hanim

 

[Colin Legg]

If you can assume that you want to delete all error values (ie. #DIV/0! etc, not just #N/A) then you can use the

Range.SpecialCells() method to get a reference to the cells containing formula errors and then delete them.

[pre]Sub foo()

Dim rngToCheck As Range, rngToDelete As Range

Application.ScreenUpdating = False

'change Sheet1 to the codename of the sheet you want to check
With Sheet1

Set rngToCheck = .Range(.Cells(6, "O"), .Cells(500, "U"))

'if there are no error cells then there will be an error
On Error Resume Next
Set rngToDelete = rngToCheck.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

'allow for overlapping ranges
If Not rngToDelete Is Nothing Then _
Intersect(.Range("A:A"), rngToDelete.EntireRow).EntireRow.Delete

End With

Application.ScreenUpdating = True

End Sub

[/pre]
Notes:


Change Sheet1 to the codename of the target sheet.

If O6:U500 contains constants rather than formulas then change xlCellTypeFormulas to xlCellTypeConstants

 

[hanim554]

Dear Colin legg,


Really great.... thanks much appreciated.


Regards

Hanim