Code runs fine for me copied and pasted directly from your post. How long is
it running before you Ctrl-Alt Del? If you have several thousands of rows it
could take this a while to run. To see if it is looping properly, you could
put a debug.print r in somewhere between your For and Next statements, then
hit Ctrl-Break during execution and see what is happening to r.
"KHashmi316" wrote:
The macro DeleteDuplicateRows suggested on this site
(http://www.cpearson.com/excel/deleting.htm) *seems* to have an
infinite loop error. Not too sure what the real glitch is but upon
running, my version of Excel (2002) goes into "hourglass mode"
requiring Crt+Alt+Del.
The macro (and its description) is presented below.
Thx for any info you can provide!
-KH
To use, select a single-column range of cells, comprising the range of
rows from which duplicates are to be deleted, e.g., C2:C99. To
determine whether a row has duplicates, the values in the selected
column are compared. Entire rows are not compared against one another.
Only the selected column is used for comparison. When duplicate values
are found in the active column, the first row remains, and all
subsequent rows are deleted.
Public Sub DeleteDuplicateRows()
'
' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.
Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Col = ActiveCell.Column
If Selection.Rows.Count 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1
Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
--
KHashmi316
------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=376776