Macro to deduplicate based on A and delete?

[cloobless]

Hello -- I've been working on this since this morning (starting in a batch file, then resorting to AutoHotkey). I know it's simple, but...so am I.

I have a .csv file that is automaticallly generated throughout the day in the following format. It is a concatenation of a bunch of other files. When the file is created, I need to very quickly scrub it, and I think the fastest way to do this is using a macro, but I'm open to any suggestion that works.

What I would like to do is the following:

1. Delete all instances of the header (Music,Description,%Length (1 Track),Category Style: Code) -- there will be multiple instances in the data.
2. Delete all instances of the footer (Sorted by MusicMaker) -- will be multiple
3. Delete all duplicates using the first value (Music) as the comparison. So even if the other values are different, delete all but one instance of "BACH".
4. Collape the list so that empty rows are deleted.

Any help offered is deeply appreciated. Thank you.

The initial data looks like this:
A

Music,Description,%Length (1 Track),Category Style: Code

BACH,"Classical Hits",70.679012,47.7678

LEDZEP,"Rock",14.666667,5912241.4000

RUSH,"Canandian Stuff",197.0538,224130.0920

Sorted by MusicMaker

I would like it to end up looking like this:

BACH

LEDZEP

RUSH

 

[cloobless]

This macro assumes that you have a header in cell A1 and your data starts in row 2.

Sub DeleteDups()
    Application.ScreenUpdating = False
    Dim lRow As Long, v As Variant, i As Long, dic As Object
    On Error Resume Next
    Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A1:A" & lRow).AutoFilter Field:=1, Criteria1:= _
        "=Music,Description,%Length (1 Track),Category Style: Code", Operator:=xlOr, Criteria2:="=Sorted by MusicMaker"
    ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
    Range("A1").AutoFilter
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("A2:A" & lRow).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = UBound(v) To LBound(v) Step -1
        If Not dic.exists(Split(v(i, 1), ",")(0)) Then
            dic.Add Split(v(i, 1), ",")(0), Nothing
        Else
            Rows(i + 1).Delete
        End If
    Next i
    Range("A2").Resize(dic.Count) = Application.Transpose(dic.keys)
    Application.ScreenUpdating = True
End Sub

Wow! This runs nearly perfectly. It only happens to retain the header and footer rows, but I will work on that with your code to see if I can figure it out.
I can't tell you how helpful this is and how much I appreciate it. THANK YOU.

 

[cloobless]

I know my p

This macro assumes that you have a header in cell A1 and your data starts in row 2.

Sub DeleteDups()
    Application.ScreenUpdating = False
    Dim lRow As Long, v As Variant, i As Long, dic As Object
    On Error Resume Next
    Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A1:A" & lRow).AutoFilter Field:=1, Criteria1:= _
        "=Music,Description,%Length (1 Track),Category Style: Code", Operator:=xlOr, Criteria2:="=Sorted by MusicMaker"
    ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
    Range("A1").AutoFilter
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("A2:A" & lRow).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = UBound(v) To LBound(v) Step -1
        If Not dic.exists(Split(v(i, 1), ",")(0)) Then
            dic.Add Split(v(i, 1), ",")(0), Nothing
        Else
            Rows(i + 1).Delete
        End If
    Next i
    Range("A2").Resize(dic.Count) = Application.Transpose(dic.keys)
    Application.ScreenUpdating = True
End Sub

I know this reply will be deleted, too, but I wanted to let you know that the script indeed does correctly exclude the header/footers. It was an error on my part. Thanks again!

 

[mumps]

You are very welcome.