Deleting records from Excel through a macro

Copper Contributor

Hi,

 

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
Next

2 Replies

@KristelNulens 

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"
    Range("A2:A3000").EntireRow.Delete
    Range("A1:A3000").AutoFilter
Thank you Hans for helping me out with this! This very simple piece of code will save me a ton of work! :)