Solved: Im sure it can be done.. Delete Duplicate Rows

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Dreambringer

Thread Starter
Joined
Jan 19, 2005
Messages
1,351
Ok another challenge.. I wish I could do this myself, but I struggle with the code.

Is there a way to have a macro check the line below it, and if it is a duplicate row, delete it :)
 
Joined
Jul 25, 2004
Messages
5,458
Hi, yes. What exactly are your requirements here? Where are the values being checked? Are they in a single column? Which one? Will this need to be performed often? A sample file would help, but is not necessary, just information.

And you want to keep the first instance, delete the rest?
 
Joined
Aug 5, 2005
Messages
3,086
Please don't think this is me snubbing you.

There's a site created by Anne (Dreamboat) called VBAExpress, and it's a fantastic resource for VBA beginners--especially with regard to the Excel and Word object models. I use it a lot for common requirements (like this one), because there is a Knowledge Base code library with code and articles written by experienced programmers. The mission of the site is to serve as a code resource for people with no knowledge of VBA but who need a macro. I'd say all the code there is worth studying.

That said, here is some modified code from this VBAX article:

Code:
Sub DeleteDuplicateEntries()
  Dim Cell As Range, Cel As Range, N&

  Application.ScreenUpdating = False

  N = 0

  For Each Cell In Selection
    '1st loop - (to speed things up ignore any empty cells)
    If Cell <> Empty Then
      For Each Cel In Selection
        '2nd loop - compare non-empty cel values
        'and clear contents if it's a duplicated value
        If Cel <> Empty And _
           Cel.Value = Cell.Value And _
           Cel.Address <> Cell.Address Then
             Cel.EntireRow.Delete
             N = N + 1
        End If
      Next Cel
    End If
  Next Cell

  Application.ScreenUpdating = True

  MsgBox "There were " & N & " duplicated entries deleted"
End Sub
HTH

chris.

[edit]
I guess I should include usage notes :eek:

This will run on any selection, so be careful with it. For example, in a multi-dimensional range (i.e., rows and columns included), it might not remove the appropriate row. It works best if you select a range column-wise. If you select more than one column and there is a duplicate value within the row but the row itself is unique, the row will still get deleted.
[/edit]
 
Joined
Jul 25, 2004
Messages
5,458
cristobal03 said:
Please don't think this is me snubbing you.
Who, me? Hope you don't think that. I don't. :cool:

I ask those questions as it could change the way of the code. Yes the KB is great, and I probably should have put a link to it. (Wonder if they'll crucify me, being an Admin.. :eek: )

Also, another way to get unique values is to do Data | Filter | Advanced Filter | Unique values..
 

Dreambringer

Thread Starter
Joined
Jan 19, 2005
Messages
1,351
Sure I attached a sample file.

I am looking for something to check the row, not just one column.
 

Attachments

Joined
Aug 5, 2005
Messages
3,086
No, sorry for the confusion Zack. I meant, I didn't want to offend Dreambringer by referring to another site for commonly-used code.

I think it's a great resource with code models worth studying, especially for beginner to intermediate developers like myself. I got bubble sort code and directory listing code from there, among other things, both of which I use extensively now. But more importantly, I've incorporated the understanding of those applications of objects into my VBA development. IMO there's no teacher or book that can compare to a well-written procedure that solves a common, everyday requirement.

Anyway, I guess I'm done. I just meant, I wasn't blowing the OP off or intending to be condescending, or anything.

chris.
 

Dreambringer

Thread Starter
Joined
Jan 19, 2005
Messages
1,351
No cris not at all, dont worry about that, I am really trying to learn it, but its alot to take on :) but very interesting reading, and I always enjoy being sent to sites that will help me answer my own questions!
 
Joined
Aug 5, 2005
Messages
3,086
So you want to check the sheet, and if an entire row is a duplicate (not just one column in the row) you want to delete it, correct?

I wonder if this hasn't already been done, post back if that's your requirement and I'll see if I can't come up with somethin.

Zack, in the article you linked, does that run faster because it uses (compiled) built-in Excel features rather than uncompiled VBA code to manage the comparisons? That's the only way I could figure.

chris.
 
Joined
Jul 25, 2004
Messages
5,458
It's super-fast because it is using the native features of Excel and not manually looping through a range with a For Each loop. Traditionally such loops are much slower. The lLastRow and lLastCol are excellent ways of getting the last row/column in a range. The Sort feature, lightning quick. The slowest part of that would be the helper column and the copy/paste values. I'm a big fan of AutoFilter, we cannot beat it's speed and functionality with VBA; there have been those that have tried.

Looping is good, but (IMHO) should be strayed away from wherever possible. It may not be that big of deal for small sets of data, but for large data sets, you would probably notice a difference.
 
Joined
Sep 4, 2003
Messages
4,912
It sounds like you want to delete entire duplicate rows that consist of duplicate values that span over several columns?

You can always use a helper column in the sheet to concatanate the individual cells using a fomula (=A9&B9&C9&D9) and then use the macro code in the links above to delete the duplicate rows in the helper column. Once this is done you can autofilter the non-blank rows and values. I'm not sure if there is an easier way to do this.

Rollin
 
Joined
Jul 25, 2004
Messages
5,458
I think you hit it spot on Rollin', unless we hear otherwise from the OP (which is all I think we're waiting on).
 

Dreambringer

Thread Starter
Joined
Jan 19, 2005
Messages
1,351
Sorry it too so long to get back to you guys, thanx for all your help.

Rollin is correct, "duplicate values that span over several columns"

Here is where I am getting the data.

We are combining lists and some data may be duplicated from one list to another, so when they are combined into 1 list, we are takling 20k rows of data, so I was looking for a faster way to go thru and delete duplicate rows
 
Joined
Jul 25, 2004
Messages
5,458
Dreambringer said:
Rollin is correct, "duplicate values that span over several columns"
Can you give an example, as per your sample workbook?


And HAPPY BIRTHDAY ROLLIN'!!!! :D
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top