Frozen Macro (Loops)

Hi Everyone,

I have a problem with stopping the execution of a macro in Excel. I don’t know if I’m stuck in an infinite loop or what.

Can someone please give me some guidance?

Ever

Here is the code that I’m using. This code finds duplicates in a Range and then shifts up to account for missing row. But it gets frozen.

VB Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim iListCount As String
    Dim iCtr As Integer
 
  ' Get count of records to search through.
      iListCount = Sheets("Sheet1").Range("A1:A100").Rows.Count
      Sheets("Sheet1").Range("A1").Select
     ' Loop until end of records.
        Do Until ActiveCell = ""
          ' Loop through records.
            For iCtr = 1 To iListCount
         ' Don't compare against yourself.
         ' To specify a different column, change 1 to the column number.
           If ActiveCell.Row <> Sheets("Sheet1").Cells(iCtr, 1).Row Then
         ' Do comparison of next record.
            If ActiveCell.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then
            ' If match is true then delete row.
                 Sheets("Sheet1").Cells(iCtr, 1).Delete xlShiftUp
               ' Increment counter to account for deleted row.
               iCtr = iCtr + 1
         End If
      End If
   Next iCtr
   ' Go to next record.
   ActiveCell.Offset(1, 0).Select
 Loop
End Sub

Ever :blush:


Edit: Added [vbcode][/vbcode] tags for clarity. - Hack

You need to disable events while your code is running.

Insert

VB Code:

Application.EnableEvents = False

at the start of your code and insert

VB Code:

Application.EnableEvents = True

at the end.

This will stop your code from calling itself.

Moved from ClassicVb.

Have you been able to determine on what line it freezes up?

Hack,

No I have not been able to determine which line freezes.

Ever :ehh:

Declan,

I will give this a try.

Thanks.

Ever ;)

Declan,

I tryed your code insertions and the same issue has occurred.

Ever

Can you post your revised code?

(merged duplicate threads into one)

Your code freezes because you have multiple blank cells in Column 1 within rows 1-100. If you only have values within, say, the first 5 rows, then when it gets to row 6 it compares it to row 7, they are a match (both blank) so it deletes row 7 and compares row 6 to the new row 7, which is also blank so it deletes row 7 and compares row 6 to the new row 7, which is also blank so it deletes row 7 and compares row 6 to the new row 7, which is also blank so it deletes row 7 and compares row 6 to the new row 7, which is also blank, and so on.

It is matching a blank cell to a blank cell and deleting it, then comparing the blank cell to the new blank cell.

Thank you so much.

That totally makes sense.

Duh

:eek: