Deleting duplivate rows

[shyamvinjamuri]

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

 

[jim may]

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]

Thanks Jim.
This sheet is updated many times a day. I am looking for VBA to delete duplicates at Workbook Open.

 

[VoG]

Adapted from http://support.microsoft.com/default.aspx?scid=kb;en-us;240077&Product=xlw2K

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

Private Sub Workbook_Open()
Call DelDups_OneList
End Sub

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

 

[jim may]

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]

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.

 

[shyamvinjamuri]

Thanks VoG II and Jim

 

[Legacy 14611]

No need to sort and also deletes rows with blanks in ColA, altho' can easily leave these in if you like:
Try

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