Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter Macro Failing | Excel Discussion (Misc queries) | |||
Inputbox macro filter using "Contains" | Excel Discussion (Misc queries) | |||
Advanced Filter Macro | Excel Discussion (Misc queries) | |||
Advanced Filter Macro | Excel Discussion (Misc queries) | |||
VB Application for Advanced filter Macro | Excel Programming |