Deleting duplivate rows

shyamvinjamuri

Board Regular
Joined
Aug 2, 2006
Messages
174
My sheet has the following:
Column A has file number and columns B thru Z have data.
How do I delete the entire row if a duplicate file number in Column A?

Please help.
Thanks
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,483
Back Up your Data first before trying ANYTHING (New to you) !!!

Temporarily insert a "Helper-Column" to the right of Column A (you will later delete the column)...

In new cell B2 (assumes header in B1) enter:

=COUNTIF($A$2:$A2,A2)>1 And copy down as far as is needed

All Cells displaying FALSE are Unique, All Cells Displaying TRUE are Duplicates,

Use the AutoFilter feature to Select all Cells in Column B = TRUE;
Delete them all - Unfilter and all should be FALSE;

Delete Column B

Jim May
 

shyamvinjamuri

Board Regular
Joined
Aug 2, 2006
Messages
174
Thanks Jim.
This sheet is updated many times a day. I am looking for VBA to delete duplicates at Workbook Open.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Adapted from http://support.microsoft.com/default.aspx?scid=kb;en-us;240077&Product=xlw2K

Code:
Sub DelDups_OneList()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through.
iListCount = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Sheets("Sheet1").Range("A1").Select
' Loop until end of records.
Do Until ActiveCell = ""
   ' Loop through records.
   For iCtr = 1 To iListCount
      ' Don't compare against yourself.
      ' To specify a different column, change 1 to the column number.
      If ActiveCell.Row <> Sheets("Sheet1").Cells(iCtr, 1).Row Then
         ' Do comparison of next record.
         If ActiveCell.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then
            ' If match is true then delete row.
            Sheets("Sheet1").Cells(iCtr, 1).EntireRow.Delete xlShiftUp
               ' Increment counter to account for deleted row.
               iCtr = iCtr + 1
         End If
      End If
   Next iCtr
   ' Go to next record.
   ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

which goes in an ordinary module.

Then right click the Excel logo, select View Code and paste in

Code:
Private Sub Workbook_Open()
Call DelDups_OneList
End Sub

Close the code window, save and close the workbook then re-open it.
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,483

ADVERTISEMENT

VoG II
aren't we assuming here that Columns A's data is previously sorted,
ascending? And if not, wouldn't there be a problem?
Thanks,
Jim
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Jim

Not according to the MS article that I linked to. However, there mustn't be any blank cells - "If your list does contain empty cells, sort the data in ascending order so that the empty cells are all at the end of your list. ".

I tested the code with an unsorted list and it worked fine.
 
L

Legacy 14611

Guest
No need to sort and also deletes rows with blanks in ColA, altho' can easily leave these in if you like:
Try
Code:
Sub deldup()
n = [a65536].End(xlUp).Row
Set a = Range("A1", Cells(n, "z"))
Set z = CreateObject("Scripting.Dictionary")
For i = 1 To n
If Not z.exists(a(i, 1).Value) And Not IsEmpty(a(i, 1)) Then
    z.Add a(i, 1).Value, Empty
    a.Rows(i).Copy a.Rows(z.Count)
End If
Next i
If Not z.Count = n Then Range(Cells(z.Count + 1, 1), Cells(n, "z")).ClearContents
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,472
Messages
5,601,854
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top