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
' 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
' Go to next record.
Application.ScreenUpdating = True
which goes in an ordinary module.
Then right click the Excel logo, select View Code and paste in
Private Sub Workbook_Open()
Close the code window, save and close the workbook then re-open it.
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.
No need to sort and also deletes rows with blanks in ColA, altho' can easily leave these in if you like:
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
If Not z.Count = n Then Range(Cells(z.Count + 1, 1), Cells(n, "z")).ClearContents