Deleting records from Excel through a macro

Question

Hi,&nbsp;I'm new to VBA and macros and need some help with the following:&nbsp;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&nbsp;'1601200-001' are being deleted, but not all.&nbsp;Set MR = Range("A1:A3000")For Each cell In MRIf cell.Value = "1601200-001" Then cell.EntireRow.DeleteNext

hansvogelaar · Answer

KristelNulens&nbsp;
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" &amp; r).Value = "1601200-001" Then
            Range("A" &amp; 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

kristelnulens · Answer

Thank you Hans for helping me out with this! This very simple piece of code will save me a ton of work! 🙂

Forum Discussion

Deleting records from Excel through a macro

2 Replies