Is it possible pointing the selection to a reference cell instead of the drop
down list?

Can do it with code. How familiar are you with code. If given an example can
you adapt that to your needs? If you need extensive help then can you post a
bit more information:

The columns Id range of the autofilter data.
The column that you want to set the filter.
The cell address with the criteria.
How you want to start the code? (With a command button or on selection of
the cell with the criteria.)

--
Regards,

OssieMac


"Seeker" wrote:

> Is it possible pointing the selection to a reference cell instead of the drop
> down list?

See my code examples here
http://www.rondebruin.nl/copy5.htm

See the comments in the code


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Seeker" <(E-Mail Removed)> wrote in message news:E0BF7AD4-7FA0-4B46-B7A6-(E-Mail Removed)...
> Is it possible pointing the selection to a reference cell instead of the drop
> down list?

Ron,
If I didn’t miss anything, I only located a pre-defined value in your
example code, what I am looking for is something like Criteria1:="sheet1.A1".
However, your lists are treasure to me, hope your don’t mind, I have
bookmarked it for future reference. Thanks again for your help.
Regards


"Ron de Bruin" wrote:

> See my code examples here
> http://www.rondebruin.nl/copy5.htm
>
> See the comments in the code
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
>
>
> "Seeker" <(E-Mail Removed)> wrote in message news:E0BF7AD4-7FA0-4B46-B7A6-(E-Mail Removed)...
> > Is it possible pointing the selection to a reference cell instead of the drop
> > down list?
>

Ossie,
My knowledge in coding is from the “macro recorder” only, am able to twist
the recorded code a bit to fit my project. My data range is A:X in sheet
named “Records”. I intended to have 5 reference cells with drop down list
(drop down list is done already) in sheet named “Oda Input”, the listing are
choices including (All) for end-users’ selection. After end-users selected
filter criteria within these five cells, they need to press the command
button to activate filter macro and copy result of filtering to sheet named
“Print” for screen viewing and print it out. I tried advanced filter but I
cannot pre-define the criteria range to select all data.

If you don’t mind, would you please also check for me is there anything I
can do to improve the macro running time? Although this macro produces my
expected result, it halts for minutes and screen showing “filter” keeps on
flashing before macro runs to the end.
Sheets("Records").Select
Range("A1:X65536").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("I1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, SortMethod:=xlStroke,
DataOption1:= _
xlSortNormal, DataOption2:=xlSortNormal
Selection.AutoFilter Field:=3, Criteria1:="<>"
Thanks in advance.
Best Regards


"OssieMac" wrote:

> Can do it with code. How familiar are you with code. If given an example can
> you adapt that to your needs? If you need extensive help then can you post a
> bit more information:
>
> The columns Id range of the autofilter data.
> The column that you want to set the filter.
> The cell address with the criteria.
> How you want to start the code? (With a command button or on selection of
> the cell with the criteria.)
>
> --
> Regards,
>
> OssieMac
>
>
> "Seeker" wrote:
>
> > Is it possible pointing the selection to a reference cell instead of the drop
> > down list?

You see this in the first code example
http://www.rondebruin.nl/copy5_1.htm


'This will use the cell value from A2 as criteria
'My_Range.AutoFilter Field:=1, Criteria1:="=" & Range("A2").Value

Replace this part with the above

'Filter and set the filter field and the filter criteria :
'This example filter on the first column in the range (change the field if needed)
'In this case the range starts in A so Field 1 is column A, 2 = column B, ......
'Use "<>Netherlands" as criteria if you want the opposite
My_Range.AutoFilter Field:=1, Criteria1:="=Netherlands"


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Seeker" <(E-Mail Removed)> wrote in message news:5DB13988-6D5D-4D4E-93D3-(E-Mail Removed)...
> Ron,
> If I didn’t miss anything, I only located a pre-defined value in your
> example code, what I am looking for is something like Criteria1:="sheet1.A1".
> However, your lists are treasure to me, hope your don’t mind, I have
> bookmarked it for future reference. Thanks again for your help.
> Regards
>
>
> "Ron de Bruin" wrote:
>
>> See my code examples here
>> http://www.rondebruin.nl/copy5.htm
>>
>> See the comments in the code
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>>
>>
>> "Seeker" <(E-Mail Removed)> wrote in message news:E0BF7AD4-7FA0-4B46-B7A6-(E-Mail Removed)...
>> > Is it possible pointing the selection to a reference cell instead of the drop
>> > down list?
>>

Hello again,

Ron's answers do provide all the info but as it appears that you are
struggling with the code I will attempt to write some code for you that
should do the trick. However, in the mean time you might like to answer my
question below so that I can answer your specific question "please also check
for me is there anything I can do to improve the macro running time?"

Range("A1:X65536").Select
The above line is possably the biggest problem. I suggest that you are
sorting to the last row because you have data with a variable number of rows.
If you can nominate a column that will always have data in all rows then we
can sort based on the actual data. (There is a command UsedRange but I find
it unreliable if there is any formatting in cells outside the actual data
range.)

--
Regards,

OssieMac

Ron,
Thanks again and sorry about my oversight on that line. I will try again.
Million thanks again.

"Ron de Bruin" wrote:

>
> You see this in the first code example
> http://www.rondebruin.nl/copy5_1.htm
>
>
> 'This will use the cell value from A2 as criteria
> 'My_Range.AutoFilter Field:=1, Criteria1:="=" & Range("A2").Value
>
> Replace this part with the above
>
> 'Filter and set the filter field and the filter criteria :
> 'This example filter on the first column in the range (change the field if needed)
> 'In this case the range starts in A so Field 1 is column A, 2 = column B, ......
> 'Use "<>Netherlands" as criteria if you want the opposite
> My_Range.AutoFilter Field:=1, Criteria1:="=Netherlands"
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
>
>
> "Seeker" <(E-Mail Removed)> wrote in message news:5DB13988-6D5D-4D4E-93D3-(E-Mail Removed)...
> > Ron,
> > If I didn’t miss anything, I only located a pre-defined value in your
> > example code, what I am looking for is something like Criteria1:="sheet1.A1".
> > However, your lists are treasure to me, hope your don’t mind, I have
> > bookmarked it for future reference. Thanks again for your help.
> > Regards
> >
> >
> > "Ron de Bruin" wrote:
> >
> >> See my code examples here
> >> http://www.rondebruin.nl/copy5.htm
> >>
> >> See the comments in the code
> >>
> >>
> >> --
> >>
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl/tips.htm
> >>
> >>
> >>
> >>
> >> "Seeker" <(E-Mail Removed)> wrote in message news:E0BF7AD4-7FA0-4B46-B7A6-(E-Mail Removed)...
> >> > Is it possible pointing the selection to a reference cell instead of the drop
> >> > down list?
> >>
>
>

Hi Ossie,
Once again thanks for your generosity in lending me your big hand in the
coding; I also will try myself to embed this macro for your emendation.
Regarding Range("A1:X65536").Select, I didn’t though of range problem as the
sheet “Records” was intended to keep all records and new added item will be
appended to next empty line, so I just to play safe and selected all rows. Is
it workable in replacing the code by following?

Range("A2:X").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("I1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, SortMethod:=xlStroke,
DataOption1:= _
xlSortNormal, DataOption2:=xlSortNormal
Range("A1:X1").Select
AutoFilterMode = True
Selection.AutoFilter Field:=3, Criteria1:="<>"


"OssieMac" wrote:

> Hello again,
>
> Ron's answers do provide all the info but as it appears that you are
> struggling with the code I will attempt to write some code for you that
> should do the trick. However, in the mean time you might like to answer my
> question below so that I can answer your specific question "please also check
> for me is there anything I can do to improve the macro running time?"
>
> Range("A1:X65536").Select
> The above line is possably the biggest problem. I suggest that you are
> sorting to the last row because you have data with a variable number of rows.
> If you can nominate a column that will always have data in all rows then we
> can sort based on the actual data. (There is a command UsedRange but I find
> it unreliable if there is any formatting in cells outside the actual data
> range.)
>
> --
> Regards,
>
> OssieMac
>

Sorry Ossie,
After testing the code, it should be as follow or your better way.

Sheets("Records").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

"Seeker" wrote:

> Hi Ossie,
> Once again thanks for your generosity in lending me your big hand in the
> coding; I also will try myself to embed this macro for your emendation.
> Regarding Range("A1:X65536").Select, I didn’t though of range problem as the
> sheet “Records” was intended to keep all records and new added item will be
> appended to next empty line, so I just to play safe and selected all rows. Is
> it workable in replacing the code by following?
>
> Range("A2:X").Select
> Range(Selection, Selection.End(xlDown)).Select
> Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("I1") _
> , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
> False, Orientation:=xlTopToBottom, SortMethod:=xlStroke,
> DataOption1:= _
> xlSortNormal, DataOption2:=xlSortNormal
> Range("A1:X1").Select
> AutoFilterMode = True
> Selection.AutoFilter Field:=3, Criteria1:="<>"
>
>
> "OssieMac" wrote:
>
> > Hello again,
> >
> > Ron's answers do provide all the info but as it appears that you are
> > struggling with the code I will attempt to write some code for you that
> > should do the trick. However, in the mean time you might like to answer my
> > question below so that I can answer your specific question "please also check
> > for me is there anything I can do to improve the macro running time?"
> >
> > Range("A1:X65536").Select
> > The above line is possably the biggest problem. I suggest that you are
> > sorting to the last row because you have data with a variable number of rows.
> > If you can nominate a column that will always have data in all rows then we
> > can sort based on the actual data. (There is a command UsedRange but I find
> > it unreliable if there is any formatting in cells outside the actual data
> > range.)
> >
> > --
> > Regards,
> >
> > OssieMac
> >