Deleting records from Excel through a macro

Copper Contributor



I'm new to VBA and macros and need some help with the following:


I'm trying to delete all records from an Excel file that have the value '1601200-001' in the first column. I put in the below code but this does not seem to lead to 'all' the necessary records being deleted: Only a few of records with value '1601200-001' are being deleted, but not all.


Set MR = Range("A1:A3000")
For Each cell In MR
If cell.Value = "1601200-001" Then cell.EntireRow.Delete

2 Replies


If you want to delete in a loop, you should loop backwards, otherwise you may skip records as you have found:

    Dim r As Long
    For r = 3000 To 1 Step -1
        If Range("A" & r).Value = "1601200-001" Then
            Range("A" & r).EntireRow.Delete
        End If
    Next r

Assuming that A1 won't contain the value 1601200-001, the following should be faster:

    Range("A1:A3000").AutoFilter Field:=1, Criteria1:="1601200-001"
Thank you Hans for helping me out with this! This very simple piece of code will save me a ton of work! :)