Advanced Filter Macro with InputBox use

[Guest]

I'd like to:

Highlight/Select a range say B5:B25
That contains (bob, carol, ted, ted, bob, alice, carol,
bob, ted, alice, carol......) << only 4 unique names are
in B5:B25 (bob, ted, carol alice)

Then run a macro that will using an Input box (where I
supply a cell reference, like J1 {enter}
and an Advance Filter is run and pastes to Range J1:J4
Alice, Bob, Carol
Ted (alphabetically).

Can this be done?

 

[Debra Dalgleish]

You can use code similar to the following:

'=======================
Sub GetUnique()
Dim rng As Range

If Selection.Columns.Count > 1 Then
MsgBox "Please select cells in one column only"
Exit Sub
End If
If Selection.Row = 1 Then
MsgBox "Selection cannot include row 1"
Exit Sub
End If
Set rng = Selection.Offset(-1, 0) _
.Resize(Selection.Rows.Count + 1, 1)

Columns("J:J").Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
Range("J1").Delete shift:=xlUp
Range("J1").CurrentRegion.Sort Key1:=Range("J1"), _
Order1:=xlAscending, Header:=xlGuess
End Sub
'==========================

 

[Jim May]

Debra:
Thanks so much for the code; I pledge to understand it over the weekend;
Enjoy yours.........
Jim May

 

[Debra Dalgleish]

You're welcome. The code resizes the selected range to include the cell
above, because an Advanced Filter assumes there's a heading cell.

After extracting the unique values, the fake heading cell is deleted,
then the list is sorted.

 

[Jim May]

Debra:
Sorry to report that somehow after it worked "the first time" I closed
reopened
and got Run time error 1004 Sort method of Range Class failed. Have
spent
an hour or so on Google, but no luck in solving problem. The error occurs
on the
last line of code (including "CurrentRegion").
Any thoughts?

 

[Debra Dalgleish]

Did data get exported to column J? If there's nothing there, you'd get
that error.

 

[Jim May]

Thanks Debra, but I solved whatever the problem was; again thank you so much
for the code. It's exactly what I wanted/needed..
Don't let the boogie-man get you over the weekend!!! LOL
Jim

 

[Debra Dalgleish]

Thanks, Jim, for letting me know that you were able to solve the
problem. You could also add some error handling code, as a preventive
measure.