Hi Shafiq

Try this - it looks in each cell in column c and if its an error it deletes it - a word of warning it will delete the entire row if the cell is ANY error (this can be modified) and it is only based on column c so if any other cells on that row were good they also would be deleted. Again this can be modified if needed.

Up to you if you want this as part of a bigger module or as a 'tidy up' button at the end.

Sub DeleteNAs()

Dim lr As Integer
Dim i As Integer

lr = Cells(Rows.Count, 3).End(xlUp).Row

For i = lr To 6 Step -1
Debug.Print i
If IsError(Range("c" & i).Value) Then

Rows(i).EntireRow.Delete

End If
Next i

End Sub

Purfleet

Hello,
Another way to delete all rows independently which cell has the error

Miguel

 

Private Sub DeleteRowsWithError() '  delete all rows with error in cells

Dim Firstrow As Long
Dim LastRow As Long
Dim FirstColumn As Long
Dim LastColumn As Long
Dim lRow As Long
Dim lCol As Long
Dim CalculateApp As Long
Dim AppView As Long

With Application
     CalculateApp = .Calculation
     .Calculation = xlCalculationManual
     .ScreenUpdating = False
End With

With ActiveSheet
     .Select
     AppView = ActiveWindow.View
     ActiveWindow.View = xlNormalView

     .DisplayPageBreaks = False

     Firstrow = .UsedRange.Cells(1).Row
     LastRow = GetLastRow(ActiveSheet)
     FirstColumn = .UsedRange.Cells(1).Column
     LastColumn = GetLastColumn(ActiveSheet)

     For lRow = LastRow To Firstrow Step -1
          For lCol = LastColumn To FirstColumn Step -1
               With .Cells(lRow, lCol)
                    On Error Resume Next
                    If IsError(.Value) Then .EntireRow.Delete
               End With
          Next lCol
     Next lRow

ActiveWindow.View = AppView

End With

With Application
     .ScreenUpdating = True
     .Calculation = CalculateApp
End With

End Sub

Public Function GetLastRow(ByVal Sht As Worksheet) ' finding the last Row

Dim lRow As Integer
lRow = Sht.Cells.SpecialCells(xlLastCell).Row

Do While Application.CountA(Sht.Rows(lRow)) = 0 And lRow <> 1
     lRow = lRow - 1
Loop

GetLastRow = lRow

End Function

Public Function GetLastColumn(ByVal Sht As Worksheet) ' finding the last column

Dim lCol As Integer
lCol = Sht.Cells.SpecialCells(xlLastCell).Column

Do While Application.CountA(Sht.Columns(lCol)) = 0 And lCol <> 1
     lCol = lCol - 1
Loop

GetLastColumn = lCol

End Function