Macro To Delete Duplicate Records (Rows), While Omiting Specific Columns

**Orangeworker** · 10-09-2010, 09:54 AM

Hi,

I'm looking for a simple macro to delete duplicate records (rows in Excel), but omit two columns of information in the selection. Columns that need to be included are (A > H). The columns that I need the macro to omit are (E & F).

Thanks,
Henry

**davesexcel** · 10-09-2010, 10:33 AM

Try this it Finds the duplicates in Column A but just deletes A1:D1,G1:H1 Ranges and shifts the cells up

Sub DeleteDuplicates()

    Dim x As Long, LastRow As Long, c As Range

    LastRow = Range("A65536").End(xlUp).Row
    
    For x = LastRow To 1 Step -1
    
        If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
        
            Set c = Range("A" & x)
            c.Range("A1:D1,G1:H1").Delete Shift:=xlUp
            
        End If
        
    Next x

End Sub

**MarvinP** · 10-09-2010, 10:44 AM

Hi Orangeworker,

There is a remove duplicates feature in Excel 2007 and 2010. It is under the Data tab in 2010. When run it asks which columns of data to match to be considered a duplicate. What you want may already be built into Excel.

**Orangeworker** · 10-09-2010, 10:53 AM

Hi and thanks for both replys. Yes, I've used the remove duplicate feature under the data tab before, but for some reason, even if I uncheck certain columns, it still doesn't give me the result I need for some reason.

**Orangeworker** · 10-09-2010, 11:00 AM

Marvin, thanks for the code. I haven't tried it yet, but their is going to be a lot of the same data in column A as well as column B, for example. So I need to treat each row as a "record". Do you think it will work based on this info? I'm getting ready to head back to my office now, then I will try it out.

**MarvinP** · 10-09-2010, 12:37 PM

Understanding what you think is happening compared to how Excel handles it is my ongoing obsession.

Give it a try and see what happens. It may already be built in. Make backups!