Help With Code for Duplicate Rows

[bolandd]

I would like to create a macro that will look at all the cells in my current row and determine if any other rows contain the exact same information. If they do, I want to delete all duplicate rows except for the first one.

 

[Joe4]

In Excel 2007 and higher, there is a "Remove Duplicates" button on the Data Menu that will do this.

Otherwise, you may want to check this out:
http://support.microsoft.com/kb/262277

 

[texasalynn]

take a look at this

 

[bolandd]

I know how to use the remove duplicates feature in excel.

However, I do not want to remove all duplicates.

I only want to remove duplicates of the row my cursor is currently on.

 

[Joe4]

Well, you could create some "brute force" VBA code that loops through your entire data range, checking each value to see if they are duplicates or note.

What does your data range look like?
How many columns are being used?

 

[bolandd]

This is a utility that I am trying to create to clean up imported data, thus number of rows and columns can vary.

Our HR software dumps out a text file that repeats header row or rows throughout the file.

I was thinking I could highlight the first header row, store the cell values in an array and loop through the rest of the rows looking for a match and then deleting each duplicate row.

Sample Data:

ID NAME DAYS MONTHS
1 Bill 9 3
2 Dave 8 2
3 Sue 10 5
ID NAME DAYS MONTHS
1 Bill 9 3
4 Larry 1 1
7 Randy 2 5

I want to highlight the first header row with ID, NAME, DAYS and MONTHS and remove any matching rows from the rest of the records.

I'm fairly proficient in VBA, so some general guidelines should point me in the right direction...

Thanks for looking

 

[texasalynn]

using a filter in your macro like this

Sub cleanup()
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
'filter data
    Cells.AutoFilter
    Cells.AutoFilter Field:=1, Criteria1:="ID"
        Range("A3:D" & LR).EntireRow.Delete
    Cells.AutoFilter
MsgBox "Done'"
End Sub

 

[Joe4]

I have actually had to do the exact same thing myself, especially when I receive those darn mainframe files with page breaks!

Here is how I did it. I don't always know where the data will end, but I know where the first header always start. So I start looping beneath that row. In your case, it looks like column A either has a number for the data row, or "ID" for the header rows. So I would just look through all your data, and delete any row that has a value of "ID" in columns A.

By the way, when deleting rows via a loop, it is always best to work backwards through the data, or else you could "miss" some rows (if there are ever situations where two consectutive rows need to be deleted).

So let's say that your data begins on row 2 (as row 1 is your first header row). Then the code might look something like this:

myLastRow=Cells(Rows.Count,"A").End(xlUp).Row
For i=myLastRow to 2 Step -1
    If Cells(i,"A").Value="ID" Then Rows(i).EntireRow.Delete
Next i