Excel VBA delete row based on cell value

Question

I wrote some VBA code that is meant to delete a row based on the value of a cell.

Const VENDRO_FIELD = 4

Dim rDataTable As Range
Set rDataTable = Range(Cells(1, 1), Cells(1, 1).End(xlToRight).End(xlDown))
Dim sCurrentRecord As Single
Dim sNumberOfRecords As Single
sNumberOfRecords = rDataTable.Rows.Count
Dim tVendor As String

For sCurrentRecord = 1 To sNumberOfRecords
    tVendor = rDataTable.Cells(sCurrentRecord, VENDOR_FIELD)
    If tVendor = "MTR" Then
        Selection.Rows(sCurrentRecord).EntireRow.Delete
    End If
Next sCurrentRecord

The cells containing the vendor are in column D, which is why the const is set to 4. But whenever I run this code it is deleting the wrong row. I don't know how it's selecting the row it decides to delete, so right now it seems random to me.

Does anyone know why it's not deleting the row that should be selected based off sCurrentRecord?

remove the `Selection.` and loop from the bottom up: `For sCurrentRecord = sNumberOfRecords to 1 Step -1` — Scott Craner, Aug 16 '21 at 15:37
You really need `Option Explicit` at the top of the module to flag undeclared variables. — BigBen, Aug 16 '21 at 15:44
@ScottCraner Thank you so much that worked! But could you explain why that works? I first tried your solution with just removing selection. from the loop, so it only had Rows(sCurrentRecord).EntireRow.Delete, but that didn't work until I set the loop to go from bottom up like you said to. — William Smithington, Aug 16 '21 at 15:44
Cells shift up when you delete one, so they have different row numbers once the deleting begins (and some will be skipped since they shift into already processed row numbers). — Warcupine, Aug 16 '21 at 15:46
@ScottCraner Yep, that's fixed. I was just changing up variable names when I posted to here and forgot to change that one. — William Smithington, Aug 16 '21 at 15:48
@BigBen I do have Option Explicit at the top, just didn't show it here! — William Smithington, Aug 16 '21 at 15:48
@Warcupine Ohhhhh that makes sense. I didn't think of that. Thank you! — William Smithington, Aug 16 '21 at 15:49

Excel VBA delete row based on cell value

0 Answers0