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?

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

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

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

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" wrote in message ...
Is it possible pointing the selection to a reference cell instead of the drop
down list?

Ron,
If I didnt 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 dont 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" wrote in message ...
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" wrote in message ...
Ron,
If I didnt 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 dont 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" wrote in message ...
Is it possible pointing the selection to a reference cell instead of the drop
down list?

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" wrote in message ...
Ron,
If I didnt 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 dont 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" wrote in message ...
Is it possible pointing the selection to a reference cell instead of the drop
down list?

Hi Ron,
Sorry to bother you again. I am try to use your code to produce the output.
Since I am not familiar with coding (corelation between the Dim, String
etc.), so I adopt the macro recorder's method and change your code
My_Range.AutoFilter Field:=1, Criteria1:=
to
Selection.AutoFilter Field:=5, Criteria1:="=" & Sheets("Oda
Input").Range("J19").Value
However, I found the filter can point to the value in cell J19 but it
doesn't (click) to make it effective, thus all data are hide.
Is there any additional action need to be taken to activate this filtering
pleasse?
Rgds


"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" wrote in message ...
Ron,
If I didnt 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 dont 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" wrote in message ...
Is it possible pointing the selection to a reference cell instead of the drop
down list?