DeleteDuplicateRows: problem with this macro

[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

 

[K Dales]

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.

 

[Jim Cone]

KH,

The macro works fine for me on XL2002.
All of the code I've found on the Chip Pearson web site does
what it says it will. I am indebted for the help that site has
provided over the years.

It is possible that the code is working but taking a very long
time to complete. If you a have a large amount of data on
the worksheet and have selected an entire column, the code
could be struggling. In addition, turning off the display
of page breaks could speed things up.

Does the code work for you if you only select say 100 rows?
If so, give the slightly modified version below a try and
see if it helps.

Jim Cone
San Francisco, USA

'-------------------------
Sub xxx()
Dim r As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
If Rng.Rows.Count = Rows.Count Then '***
MsgBox "Please do not select an entire column. ", vbInformation, " Info"
Set Rng = Nothing
Exit Sub
End If
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

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
ActiveSheet.DisplayPageBreaks = False '***
Rng.Rows(r).EntireRow.Delete
End If
Next r

EndMacro:
Set Rng = Nothing
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
'-----------------------------


"KHashmi316" <[email protected]>
wrote in message
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]

Thx for everyone's replies -- and you were all correct: it DOES work
but one has to wait it out.

As you suspected, I do have thousands of rows, and many of the sheets
would *like* to use this macro on are no different. The one I jus
tested had 1400 rows, which took about 4 minutes on an XP Pro 2.4GH
laptop. Other sheets I use have as many as the max allowable no. o
rows (65536).

I would like to use the DeleteDuplicateRow macro as part o
more-comprehensive "super-macro" I have designed (which takes quite
bit of processing time itself). Unfortunately, given the speed o
DeleteDuplicateRow, it simply takes too long to be of use to me. Well
not unless anyone knows of a way to speed it up -- or know another
quicker alternative.

Thx again for everyone's input!
-K

 

[STEVE BELL]

If you haven't already used these - they help speed up macros

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
[ your code ]

Application.ScreenUpdating = True
Application.Calculation=xlCalculationAutomatic

 

[KHashmi316]

If you haven't already used these - they help speed up macros

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
[ your code ]

Application.ScreenUpdating = True
Application.Calculation=xlCalculationAutomatic

Hmm... I tested this scheme by running a stop-watch on the code both
with and without the "speed-up" tweak. Using the "sped-up" method
actually took 10 seconds longer. " I'm not sure whether you meant
putting the tweak around just the DeleteDuplicateRows macro or the
"super" macro I'm using which calls on several sub-maros including
DeleteDuplicateRows. I tweaked the latter.

Thx for your reply and any further light you can shed.

 

[STEVE BELL]

I am surprised. Usually these help with speed.
(need to apologize - you did have it in your posted code)

Usually I put all my code in the middle. So in your case it would be in the
Super Macro.

I have been playing with some alternate schemes but the calculation time
just climbs after about
5,000 lines.

Don't know if it could work, but try to sort on your column and delete rows.

Try this on a copy of your workbook. Do it manually first and see if it is
practical.

Another alternative is to look at filtering. With Advanced Filtering, you
can pull out unique records only.
This will still take some time, but am not sure of what it would be.

--
steveB

Remove "AYN" from email to respond

If you haven't already used these - they help speed up macros

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
[ your code ]

Application.ScreenUpdating = True
Application.Calculation=xlCalculationAutomatic

Hmm... I tested this scheme by running a stop-watch on the code both
with and without the "speed-up" tweak. Using the "sped-up" method
actually took 10 seconds longer. " I'm not sure whether you meant
putting the tweak around just the DeleteDuplicateRows macro or the
"super" macro I'm using which calls on several sub-maros including
DeleteDuplicateRows. I tweaked the latter.

Thx for your reply and any further light you can shed.