K
KHashmi316
The macro DeleteDuplicateRows suggested on this sit
(http://www.cpearson.com/excel/deleting.htm) *seems* to have a
infinite loop error. Not too sure what the real glitch is but upo
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 o
rows from which duplicates are to be deleted, e.g., C2:C99. T
determine whether a row has duplicates, the values in the selecte
column are compared. Entire rows are not compared against one another.
Only the selected column is used for comparison. When duplicate value
are found in the active column, the first row remains, and al
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) >
Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Su
(http://www.cpearson.com/excel/deleting.htm) *seems* to have a
infinite loop error. Not too sure what the real glitch is but upo
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 o
rows from which duplicates are to be deleted, e.g., C2:C99. T
determine whether a row has duplicates, the values in the selecte
column are compared. Entire rows are not compared against one another.
Only the selected column is used for comparison. When duplicate value
are found in the active column, the first row remains, and al
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) >
Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Su