How do I loop back to my current cell if my conditions are met?

Question

this is my first post on SO, so I hope I am doing this correctly.

I am trying to delete rows from my data that meet the following conditions:

First column's value is different from the cell below it
Third column's value is equal to the cell below it

A sample of my code looks like this:

For Each Cell In MyRangeA3
        If Cell.Value <> Cell.Offset(1).Value Then
            If Cell.Offset(, 2).Value = Cell.Offset(1, 2).Value Then
            Cell.Offset(1).EntireRow.Delete
            End If
        End If
Next Cell

The problem I am facing is that my macro goes to the next cell as such I have to rerun my macro again in order to remove the next row that meets my conditions. What should I add to my code in order to do this in one run?

**1.** [Find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) **2.** Use a reverse loop `For i = Lastrow to 1 Step -1` and then match and delete the rows. — Siddharth Rout, Jun 01 '21 at 05:12
As mentioned by @SiddharthRout: removing items from a list is a well-known trap: when you start at the beginning of the list and you proceed, you might not remove all desired items. Therefore you need to start at the end of the list and run through the list in the opposite direction. :-) — Dominique, Jun 01 '21 at 06:07
@SiddharthRout, may I know what do you mean by Step -1? Sorry, I am relatively new to VBA. — Oliver Theseira, Jun 01 '21 at 09:49
https://stackoverflow.com/questions/19687018/what-does-the-to-and-step-mean-in-vba — Siddharth Rout, Jun 01 '21 at 10:37

score 0 · Accepted Answer · answered Jun 01 '21 at 05:35

0

You can collect matching cells and then delete appropriate rows:

' ... your code    

Dim toDel As Range
For Each cell In MyRangeA3
    If cell.Value <> cell.Offset(1).Value Then
        If cell.Offset(, 2).Value = cell.Offset(1, 2).Value Then
            If toDel Is Nothing Then    'collect cells to further delete
                Set toDel = cell.Offset(1)
            Else
                Set toDel = Union(toDel, cell.Offset(1))
            End If
        End If
    End If
Next cell
' delete all rows for appropriate collected cells
' debug the script before actually deleting lines with, e.g. 
' toDel.EntireRow.Interior.ColorIndex = 3 instead toDel.EntireRow.Delete
If Not toDel Is Nothing Then toDel.EntireRow.Delete

' your code ...

answered Jun 01 '21 at 05:35

Алексей Р

7,507
2
7
18

I tried your code but I ran into "Run-time error '424': Object required" at the last line – Oliver Theseira Jun 01 '21 at 08:37
@OliverTheseira The error occurred because you did not copy the first line `Dim toDel As Range` – Алексей Р Jun 01 '21 at 09:44
1

If you skip the line `Dim toDel As Range`, then toDel is implicitly declared as Variant with the initial value Empty, and if no lines suitable for deletion are found, then the value of toDel remains Empty, which causes an error. If you declare `Dim toDel As Range`, then the initial value of toDel will be Nothing, and if no rows suitable for deletion are found, then the condition` If Not toDel Is Nothing` will not be met and no attempt will be made to delete rows from the empty range. – Алексей Р Jun 01 '21 at 09:50
How peculiar, I did include it into my list of variable declarations above the rest of my code and it didn't work. After I moved it down as per your answer, it works fine. Can you explain to me why is this so? Also, why does declaring "Cell as Range" in my list of variable declarations above work just fine. – Oliver Theseira Jun 01 '21 at 10:01
@OliverTheseira I cannot see all of your code as it is not shown. – Алексей Р Jun 01 '21 at 10:04
1

@OliverTheseira most likely, you have declared variables separated by commas, do not specify the type after each: `Dim toDel, cell as Range`. VBA interpreted it like this: `Dim toDel as Variant, cell as Range`. Declare variables like this: `Dim toDel as Range, cell as Range` – Алексей Р Jun 01 '21 at 10:14
That is exactly what I did. I did not know VBA interpreted it like that when you separate your variables with commas. – Oliver Theseira Jun 01 '21 at 11:11
just realised that I never formally thanked you for your answer and thorough explanation, so here it is. Thanks!! – Oliver Theseira Aug 05 '21 at 17:44

How do I loop back to my current cell if my conditions are met?

1 Answers1