Add auto filter (unique records) option to macro

[eurydice88]

Hello, I have been modifying a workbook and the original macro will send to the sheet Results once the "Search" button is clicked, but I have found that this macro is displaying duplicate records. Can anyone help me put in an auto filter to find only unique records? My second workaround option is if someone can help me remove the go to/select sheet option from the "Search" macro so that a user will not automatically be sent to the Results tab and will instead hit the "Confirm Category Selection" button (which auto filters before sending the user to the Results tab). I would be forever indebted to any help people can provide, I am very very new to VBA and appreciate all the help I can get!

[pike]

Hi eurydice88
Can you zip the file?
It just save bytes

[eurydice88]

Hi Pike, thanks so much for your reply! I just added the zip file.

[pike]

Hi eurydice88
do you have excel 2003 or 2007?

[eurydice88]

Sorry I realized I should have added that to original post...I have 2007.

[StephenR]

You shouldn't really PM members as I think you know - you can always bump your thread.

Isn't the problem that you have duplicate records in your dataset, and can you not do something about that?

[eurydice88]

Yes, again I am very sorry, I just wasn't sure if anyone else was going to respond to the thread. You are right, I should have bumped the thread, again my apologies I was just getting worried I might not reach anyone.

Yes the issue is that once you hit the Search button, duplicate records will populate the Results tab and I have been trying (with next to no success) to solve the problem. Any help would be wonderful at this point, I normally like to be proactive about things and I hate bugging people, but I have tried time and time again to fix the problem but I haven't been able to Again, I am really sorry about the trouble.

[StephenR]

Don't worry, not a big deal.

Is the problem that the code is finding the unique values in col C (sub-category) when it should be doing it for category (col D)?

If that is the problem, try this. If not, come back.

Private Sub UserForm_Initialize()

With Sheets("FINAL_TABLE")
    .Range("BA:BA").Clear
    .Range("D1", .Range("D" & Rows.Count).End(xlUp)).AdvancedFilter _
                xlFilterCopy, CopyToRange:=.Range("BA1"), Unique:=True  'change BA1 to any unused column
    Me.ListBox1.RowSource = "FINAL_TABLE!" & .Range("BA2", .Range("BA2").End(xlDown)).Address
End With

End Sub

[pike]

Hi eurydice88
When you first Initialize the userform you are copying the unique list for the listbox
Why not place the total unique list into the results sheet then filter by Criteria1 in place

[eurydice88]

Thank you both so much for your help!

[pike]

Hi eurydice88
what was your solution?

[eurydice88]

Hi Pike, I added in a new definition:

Sub DelDups()
  Application.ScreenUpdating = False
  Dim deleted As Boolean
  deleted = True
  While deleted = True
    deleted = False
    For Each x In Sheets("RESULTS").Range("A2:A100")
       For Each y In Sheets("RESULTS").Range("A2:A100")
         If x.Row <> y.Row Then
           If x.Text = y.Text And y.Text <> "" Then
             Sheets("RESULTS").Rows(y.Row).Delete xlShiftUp
             deleted = True
           End If
         End If
       Next
    Next
  Wend
  Application.ScreenUpdating = True
End Sub

I've attached the finished workbook in case anyone else needs it as well.