"End If" Debug issue

[VBA_Debutant]

Hello,

Sorry to post something just to have my macro reviewed, but I have been wasting hours trying to figure out what's wrong.

I have a big file with repeated items in column B.
For example let's say that I have in column A different regions, in B different items and in C the date of item creation in the database. I don't care about the detail of items per region, I just want to keep the item creation date for each item.

I have tried to delete unnecessary lines by doing this (the file is sorted by items so all repeated items are together):

Sub TESTKeep1stLines()
Dim i As Long
Dim y As Long
i = 2

Do While IsEmpty(Cells(i, 1)) = False
y = i - 1
If Cells(i, 2).Value = Cells(y, 2).Value Then
Cells(i, 2).EntireRow.Delete
Else: i = i + 1
End If
Loop
End Sub

But it does not work (I have to hi ESC to end the macro and nothing was done). When I check the Debugger it shows the "End If" line highlighted.

Anyone has any idea what's wrong?

Thank you so much

 

[Gary McMaster]

Not a direct answer to your question but ...

When deleting rows (cells) you should work from the bottom up.

If your loop counter is at 3 and you delete row 3 then row 4 becomes row 3 and row 5 becomes row 4. When your loop counter indexes to 4 it is really addressing row 5 (just changed 4) and misses row 4 altogether because it changed to row 3 on the previous pass.

Gary

 

[shg]

That code can be fixed to work, but in the alternative (Excel 2007+)

ActiveSheet.UsedRange.RemoveDuplicates Columns:=2, Header:=xlYes

 

[John_w]

Setting up columns A-C with repeated items together in column B as you describe and with the first data in row 1, your code works for me. Here's slightly tweaked version without the y variable:

Sub TESTKeep1stLines()
    Dim i As Long
    
    i = 2
    Do While IsEmpty(Cells(i, 1)) = False
        If Cells(i, 2).Value = Cells(i - 1, 2).Value Then
            Cells(i, 2).EntireRow.Delete
        Else
            i = i + 1
        End If
    Loop
    
End Sub

When deleting rows (cells) you should work from the bottom up.

That's not necessary if you increment the row index only when you don't delete a row, as the OP's code does.

 

[Norie]

Sounds to me like you are getting an infinite loop somehow.