VBA range select error (deleting a row)

Question

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

Don't use Application.ScreenUpdating = False twice. Just use it once in the beginning and set it to True at the end. Second, just change FoundCell to check Range("I:J"). Two loops are unnecessary when you can do it with one. Third, missing s in LookIn:=xlValues in second loop. — WGS
– WGS, Commented Jul 21, 2014 at 16:25

mrbungle · Accepted Answer · 2014-07-21 16:19:16Z

1

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

answered Jul 21, 2014 at 16:19

mrbungle

1,9391 gold badge16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA range select error (deleting a row)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related