Tags: advanced, alice, b5b25, bob, carol, contains, excel, filter, highlight, inputbox, ltlt, macro, microsoft, msdn, range, select, software, ted

Advanced Filter Macro with InputBox use

On Microsoft » Microsoft Excel

14,048 words with 6 Comments; publish: Fri, 23 May 2008 06:23:00 GMT; (30678.13, « »)

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?

All Comments

Leave a comment...

  • 6 Comments
    • 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

      '==========================

      anonymous.excel.todaysummary.com.discussions.microsoft.com 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

      #1; Fri, 23 May 2008 06:25:00 GMT
    • Debra:

      Thanks so much for the code; I pledge to understand it over the weekend;

      Enjoy yours.......

      Jim May

      "Debra Dalgleish" <dsd.excel.todaysummary.com.contexturesXSPAM.com> wrote in message

      news:4182BE1C.4010306.excel.todaysummary.com.contexturesXSPAM.com...

      > 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

      > '==========================> anonymous.excel.todaysummary.com.discussions.microsoft.com 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

      >

      #2; Fri, 23 May 2008 06:26:00 GMT
    • 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" <dsd.excel.todaysummary.com.contexturesXSPAM.com> wrote in message

      > news:4182BE1C.4010306.excel.todaysummary.com.contexturesXSPAM.com...

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

      >>'==========================>>anonymous.excel.todaysummary.com.discussions.microsoft.com 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

      #3; Fri, 23 May 2008 06:27:00 GMT
    • 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" <dsd.excel.todaysummary.com.contexturesXSPAM.com> wrote in message

      news:4182BE1C.4010306.excel.todaysummary.com.contexturesXSPAM.com...

      > 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

      > '==========================> anonymous.excel.todaysummary.com.discussions.microsoft.com 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; Fri, 23 May 2008 06:28:00 GMT
    • Did data get exported to column J? If there's nothing there, you'd get

      that error.

      Jim May wrote:

      > 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" <dsd.excel.todaysummary.com.contexturesXSPAM.com> wrote in message

      > news:4182BE1C.4010306.excel.todaysummary.com.contexturesXSPAM.com...

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

      >>'==========================>>anonymous.excel.todaysummary.com.discussions.microsoft.com 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

      #5; Fri, 23 May 2008 06:29:00 GMT
    • 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.

      Jim May wrote:

      > 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" <dsd.excel.todaysummary.com.contexturesXSPAM.com> wrote in message

      > news:41838167.4020503.excel.todaysummary.com.contexturesXSPAM.com...

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

      >>that error.

      >>Jim May wrote:

      >>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" <dsd.excel.todaysummary.com.contexturesXSPAM.com> wrote in message

      >>news:4182BE1C.4010306.excel.todaysummary.com.contexturesXSPAM.com...

      >>

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

      >>'==========================>>anonymous.excel.todaysummary.com.discussions.microsoft.com 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

      >

      >

      Debra Dalgleish

      Excel FAQ, Tips & Book List

      http://www.contextures.com/tiptech.html

      #6; Fri, 23 May 2008 06:30:00 GMT