Frozen Macro (Loops)

[Everx Moorx]

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.

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


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

[DKenny]

You need to disable events while your code is running.

Insert

Application.EnableEvents = False

at the start of your code and insert

Application.EnableEvents = True

at the end.

This will stop your code from calling itself.

[Hack]

Moved from ClassicVb.

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

[Everx Moorx]

Hack,

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

Ever

[Everx Moorx]

Declan,

I will give this a try.

Thanks.

Ever

[Everx Moorx]

Declan,

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

Ever

[DKenny]

Can you post your revised code?

[si_the_geek]

(merged duplicate threads into one)

[WorkHorse]

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.

[Everx Moorx]

Thank you so much.

That totally makes sense.

Duh