Would like to Clear contents of last row if...

[auto.pilot]

The code below clears entire row contents if the word 'Unused' is found in column C. I'd like to restrict this to only the last row for each loop. ie: if 'Unused' is found in cell C13, 'Cars' is in cell C14 and 'Unused' is in C15 & C16, then clear row 15 & 16 but don't clear row 13, or anything 14 or above.

How can I do this?

Thanks
jim

Sub DeleteUnused()

    Last = Range("C55").End(xlUp).Row
    For i = Last To 8 Step -1
        If (Cells(i, "C").Value) = "Unused" Then
            Cells(i, "A").EntireRow.ClearContents
        End If
    Next i
    

End Sub

 

[ColinKJ]

Hi auto.pilot,

Try:

Sub DeleteUnused()
 
    Last = Range("C55").End(xlUp).Row
    For i = Last To 8 Step -1
        If (Cells(i, "C").Value) <> "Unused" Then Goto x
        If (Cells(i, "C").Value) = "Unused" Then
            Cells(i, "A").EntireRow.ClearContents
        End If
    Next i
 
x:
End Sub

 

[Maheshp]

Hi
Try :

Sub test()
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = LR To 8 Step -1
If Range("c" & i).Value <> "Unused" Then Exit Sub
If Range("C" & i).Value = "Unused" Then Rows(i).ClearContents
Next i
End Sub

 

[auto.pilot]

So simple, thanks.

jim