0

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?

Improve this question
8
  • 2
    remove the Selection. and loop from the bottom up: For sCurrentRecord = sNumberOfRecords to 1 Step -1 Commented Aug 16, 2021 at 15:37
  • also tSubServicer should be tVendor Commented Aug 16, 2021 at 15:43
  • 1
    You really need Option Explicit at the top of the module to flag undeclared variables. Commented Aug 16, 2021 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. Commented Aug 16, 2021 at 15:44
  • 1
    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). Commented Aug 16, 2021 at 15:46
Related questions
651
How can I avoid using Select in Excel VBA?
766
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
14
Excel VBA - read cell value from code
Load 5 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.