CODEWITHSUNDEEP
excelvbaforeach
yesIamFaded
yesIamFaded

Reputation: 2068

VBA Entirerow.Delete does not delete last cell in For Each

I have some code that deletes Rows in Excel if they are older then a chosen Date. The function works but it doesnt delete the last value.

If I run this code for example:

Dim count As Long
count = 0
Dim searchRng As Range: Set searchRng = Range("Q9:Q5000")
Dim Cell As Range

For Each Cell In searchRng
    If ((Cell.Value < CDate(TextBoxDelete.Value)) And (Cell.Value <> "")) Then              
    
    count = count + 1
    
     Debug.Print ("Cell " & count & ": " & Cell.Value & " txtbox: " & TextBoxDelete.Value)      
    
     'Cell.EntireRow.Delete  ' This line deletes the Row                                                                    
    
End If

    Next Cell

If I run this code I get the follwing output:

Cell 1: 03.03.2021 txtbox: 04.03.2021
Cell 2: 03.03.2021 txtbox: 04.03.2021
Cell 3: 03.03.2021 txtbox: 04.03.2021
Cell 4: 03.03.2021 txtbox: 04.03.2021

This is correct these are the 4 Cells that are older then the TextBox.Value

Now when I take the same function and comment in the DELETE Part the output looks like this:

Cell 1: 03.03.2021 txtbox: 04.03.2021
Cell 2: 03.03.2021 txtbox: 04.03.2021

The code is the same but as u can see it only finds 2 values now - instead of 4!

The 2 Cells that are found get deleted but the 2 other Cells are not deleted.

Has anyone encountered similar behavoir?

Edit: After all the help in the Answer section I came up with this:

For j = searchRng.count To 1 Step -1
    If ((searchRng(j).Value < CDate(TextBoxDelete.Value)) And (searchRng(j).Value <> "")) Then
    
    count = count + 1
    
    Debug.Print ("Cell " & count & ": " & searchRng(j).Value & " txtbox: " & TextBoxDelete.Value)
     
    searchRng(j).EntireRow.Delete                                                                           ' Delete Call for the Row
     
    End If
     
    Next j

Is this valid code or does this have any flaws? Running it did exactly what I needed.

Upvotes: 0

Views: 55

Answers (3)

Алексей Р
Алексей Р

Reputation: 7627

You can traverse your Range from top to bottom by collecting cells and then deleting them all at once:

Option Explicit

Sub test1()
    Dim toDel As Range, Cell As Range
    Dim searchRng As Range: Set searchRng = Range("Q9:Q5000")
    
    ' debug var; remove it and replace it further with TextBoxDelete.Value
    Dim txtBoxValue: txtBoxValue = #5/10/2021#
    
    For Each Cell In searchRng
        If Cell < CDate(txtBoxValue) And Not IsEmpty(Cell) Then
            'collect matched cells into toDel
            If toDel Is Nothing Then
                Set toDel = Cell
            Else
                Set toDel = Union(toDel, Cell)
            End If
         Debug.Print ("Cell " & toDel.Cells.count & ": " & Cell.Text & " txtbox: " & txtBoxValue)
        End If
    Next Cell
    ' next statement deletes all the rows at once
    If Not toDel Is Nothing Then toDel.EntireRow.Delete
End Sub

enter image description here

enter image description here

Upvotes: 1

Tragamor
Tragamor

Reputation: 3634

If using a loop, you generally need to use a For i = x to 1 Step -1 type loop for deleting rows. The alternative is to build up a range for deleting using Application.Unionthen delete everything in one go.

An example of how you should be able to adjust your code is below (untested);

Sub ExampleDelete()
    Dim SearchRng As Range: Set SearchRng = Range("Q9:Q5000")
    Dim i As Long, Cell As Range
    
    For i = SearchRng.Cells.count To 1 Step -1
        Set Cell = Application.WorksheetFunction.Index(SearchRng, i)
        If ((Cell.Value < CDate(TextBoxDelete.Value)) And (Cell.Value <> "")) Then
            Debug.Print ("Cell " & i & ": " & Cell.Value & " txtbox: " & TextBoxDelete.Value)
            'Cell.EntireRow.Delete  ' This line deletes the Row
        End If
    Next Cell
End Sub

Upvotes: 1

Sekuraz
Sekuraz

Reputation: 588

You are modifying the thing you are iterating over. So once you delete the first row, the second row becomes the first row, which you already processed, so the iteration continues in the original row 3, which is now the second item in the iteration.

There is the same problem but in java with some great answers here.

Upvotes: 1

Related Questions