Help with For Next

[oswalda]

I need a for next loop that cycles through the spreadsheet finding every row that has #N/A in column C and then deletes that row.

I' guessign it would look something like this


Dim testrange as range
Set testrange = C1:C300

For each row in testrange with #N/A in it

If Cell value is #N/A Delete entire Row

Next testrange

Tried all I know how to do for a few hours now - can't get anything to work.

thanks for your help

 

[rlv01]

There are a few different ways to go. Here is a simple one.

Sub DeleteNARows1()
    Dim WS As Worksheet
    Dim rng As Range
    Dim I As Long, LastRow As Long

    Set WS = ActiveSheet
    Set rng = WS.Range("C1:C300")

    LastRow = rng.Rows.Count
    For I = LastRow To 1 Step -1
        With rng.Cells(I, 1)
            If Application.IsNA(.Value) Then
                .EntireRow.Delete
            End If
        End With
    Next I
End Sub

If you are sure there are no empty cells in the range, then another possibility is

Sub DeleteNARows2()
    Dim WS As Worksheet
    Dim rng As Range

    Set WS = ActiveSheet
    Set rng = WS.Range("C1:C300")

    rng.Replace What:="#N/A", Replacement:=vbNullString, LookAt:=xlWhole
    rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[abdelfattah]

I expect this error because of problem in formula, so I think this could help you
you can try this

Sub Delete_Error()
    On Error Resume Next
    Columns("C").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End Sub