Advanced Filter Macro with InputBox use
 

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?

Advanced Filter Macro with InputBox use
 

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
'==========================

wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Advanced Filter Macro with InputBox use
 

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

"Debra Dalgleish" wrote in message
...
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
'==========================

wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Advanced Filter Macro with InputBox use
 

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 wrote:
Debra:
Thanks so much for the code; I pledge to understand it over the weekend;
Enjoy yours.........
Jim May

"Debra Dalgleish" wrote in message
...

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
'==========================

wrote:

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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html