×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VBA Delete Dupes ---SLOW

VBA Delete Dupes ---SLOW

VBA Delete Dupes ---SLOW

(OP)
Hi All,
The below code finds dupes and deletes the dupe(s) with the lowest number located in column "BL". The code works great for small set of data (8k rows). I am trying to use the code for over a 500,000 cells takes over three hours to run. Any help would be greatly appreciated.

Sub DeleteDups()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Dim sh As Worksheet, lr As Long
Sheets("A").Select
Set sh = Sheets("A")
lr = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


ColLoc = "F"
y = "BL"


For i = lr To 2 Step -1
With sh
Set fnRng = .Range(ColLoc & 2, .Cells(i - 1, y)).Find(.Cells(i, y).Value, , xlValues, xlWhole)
If Not fnRng Is Nothing Then
If fnRng.Offset(0, 58) > .Cells(i, y).Offset(0, 58) Then
.Rows(i).Delete
ElseIf fnRng.Offset(0, 58) < .Cells(i, y).Offset(0, 58) Then
fnRng.EntireRow.Delete
End If
End If
End With
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

RE: VBA Delete Dupes ---SLOW

Hi,

SORT your table on column BL.

Create a helper column with the formula, assuming that your helper column is in ZZ...

=IF(BL2=BL1,ZZ1+1,1)


Filter your table on the helper column on any value except 1.

Delete each visible row other than the heading row.

Remove the filter.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Delete Dupes ---SLOW

(OP)
Thanks Skip. I sorted column F and BL. Column F contains my IDs (which can be many from 2 to 15 duplicates) and column BL contains row number using (ROW() function.) The goal of the macro is to only keep only one unique ID with the largest row number. Unfortunately, the macro above can not handle large data set of 500,000 rows. It takes almost 2 hours to run.

RE: VBA Delete Dupes ---SLOW

Quote:

The goal of the macro is to only keep only one unique ID with the largest row number.

Why the largest?

Why not keep the FIRST? Again using Auto Filter hide 1. Delete the rest!

Two steps! Just a few seconds!

Why not???

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Delete Dupes ---SLOW

BTW that peocess can be automated. Just turn on your macro recorder and recordthose steps.

Post back with your recorded code to customize selecting the visible rows.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Delete Dupes ---SLOW

(OP)
Hi Skip,

Thanks for your help. Sorry for not being clear. I get a daily download of IDs and other details. I need to take the most recent ID. The IDs are incremented with the latest version is in the largest row number. What you provided takes the older version of the ID instead the latest version. There is no version number in the file I receive. Hoping you can help.

RE: VBA Delete Dupes ---SLOW

=Version=COUNTA(ID)

When the count of the ID equals the version, the expression will be TRUE.

Delete all FALSE rows.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Delete Dupes ---SLOW

(OP)
Hi Skip,

I applied what you suggested and it worked. Thank you for your help! Simpler is sometimes better.

RE: VBA Delete Dupes ---SLOW

👍

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close