CODEWITHSUNDEEP
excelvba
user3861414
user3861414

Reputation: 1

VBA range select error (deleting a row)

I am looking to implement a VBA routine which deletes automatically anything in column I&J which has the word 'delete' in it because this is a validation return which I have set up. I have the following code, yet my code breaks on the second loop whereby I get a subscript out of range. Haven't been able to fix it so if someone could give advice, would be much appreciated. 

Sub RemoveNA()
'THIS DELETES ALL ROWS WHERE THE VALUE IN COLUMN I IS "DELETE"
    Dim FoundCell As Range
    Application.ScreenUpdating = False
    Set FoundCell = Range("I:I").Find(what:="DELETE", LookIn:=xlValues)
    Do Until FoundCell Is Nothing
        FoundCell.EntireRow.Delete
        Set FoundCell = Range("I:I").FindNext
    Loop

    'THIS DELETES ALL ROWS WHERE THE VALUE IN COLUMN J IS "DELETE"
    Application.ScreenUpdating = False
    Set FoundCell = Range("J:J").Find(what:="DELETE", LookIn:=xlValue)
    Do Until FoundCell Is Nothing
        FoundCell.EntireRow.Delete
        Set FoundCell = Range("J:J").FindNext
    Loop

End Sub

Upvotes: 0

Views: 111

Answers (1)

mrbungle
mrbungle

Reputation: 1931

Looks like you're missing the 's' in 'LookIn:=xlValue' in the second loop.

Upvotes: 1

Related Questions