Sep 26 2022 06:16 AM
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
Sep 26 2022 07:44 AM
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
Oct 21 2022 01:43 AM