Excel macro

I have a list of names (about 11,000 in nnumber) in Excel some of which are repeated---I have done a sort on names so that the repeated names are next to each other (in a column) but I need a macro in VBA that will delete the repeated names (never more than two in number). I would appreciate some help on this since I'm not a VBA programmer--thanks, Marion McCaleb

 

Must it be a macro? If not, use a filter list, directions are here.

 

Thanks for your response!! The FILTER function is somehing I'll look at in more detail---one problem with it---for my application I need to delete both duplicate records---is this possible with the filter function?---Marion McCaleb

 

The FILTER function appears to meet my requirements David---thanks again for pointing this out to me---best wishes, Marion McCaleb

 

I spoke too soon---when I have duplicates, I want all duplicates deleted, for example if I have two identical names, I want the records for both of these names removed. My experimenting with the FILTER function removes one of the two names but not both---any way to delete both duplicate records?

 

Not with a filter unfortunately.
You'd have to write a macro. The basic algorithm would be something like:
1. Create 2 arrays, one for string data, one for integer data.
2. Create a string.
3. Populate it with each records name field.
4. Initialise the string to the value of the first record's name field.
5. Start the Outer Loop (which loops until all names have been checked against the array).
6. Outer Loop: Start the Inner Loop (which loops until all entries in the array have been checked against the current value of the string).
7. Inner Loop: Loop through the array to find all records which match the name value in the string. Store the array index (IE row reference) of each match in the integer array. End of Inner Loop.
8. Outer Loop: Change the value of the string to the next name to be checked against. End of Outer Loop
9. Delete all rows which have a row number equal to an entry in the integer array. Make sure you do this from the last record to the first, not the first to last.

Hope that is a clear to those reading as it was to me writing.

 

Thanks for your response David. I posed my question to the MrExcel website where they were kind enough to give me the following code for a macro:

Code:

Option Explicit Sub DeleteDupesDemo() Dim LstRw As Long, Rng As Range, c As Range, DltRng As Range With Sheets( "Sheet1 ") LstRw = .Cells(Rows.Count, "A ").End(xlUp).Row Set Rng = .Range( "A1:A" & LstRw) For Each c In Rng If WorksheetFunction.CountIf(Rng, c.Value) > 1 Then If DltRng Is Nothing Then Set DltRng = c Else Set DltRng = Union(DltRng, c) End If End If Next c If Not DltRng Is Nothing Then DltRng.EntireRow.Delete End With End Sub

Sorrry that the indentations don't show on the copy above. I plan to cut and paste this into a mcro and will see if it does what I want---regards, Marion McCaleb

 

You can use code tags to surround code in your postings. Highlight the code and then click on the hash (#) button in the tool bar.

The results look something like this:

Code:

Option Explicit 
Sub DeleteDupesDemo() 
  Dim LstRw As Long, Rng As Range, c As Range, DltRng As Range 
  With Sheets( "Sheet1 ") 
    LstRw = .Cells(Rows.Count,  "A ").End(xlUp).Row 
    Set Rng = .Range( "A1:A" & LstRw) 
    For Each c In Rng 
       If WorksheetFunction.CountIf(Rng, c.Value) > 1 Then 
          If DltRng Is Nothing Then 
            Set DltRng = c 
          Else 
            Set DltRng = Union(DltRng, c) 
          End If 
       End If 
    Next c 
    If Not DltRng Is Nothing Then DltRng.EntireRow.Delete 
  End With 
End Sub

If think I've split that correctly.

 

Thanks for the tip David---the code indentations are correctly shown in your response above---when I get around to trying this macro I'll let you know how it works---regards, Marion McCaleb

 

I tested the macro in the code previously shown and it works great. It is designed to delete all duplictate records by comparing the names in Colum A, Sheet 1 of the Excel database---Marion