Hi all!
It seems to me that my last question about criteria wasn't considered :(
I read this http://www.j-walk.com/ss/excel/usertips/tip044.htm but it's not
That I'll now one more try explain my problem
link to file http://aljany.com/Criterias_.jpg and my example file is here
http://aljany.com/My_Book.xls
The first column contains years, the second contains month. On these columns
Autofilter is applied. There is a form of input of filter for users. In it
we choose a year, for example 1977. Excel will filter on a year the first
column and will rotin in Autofilter in the second column collection from
months, for example «august, september, june» are avaliable. How to get this
avaliable criteria values? If the program chooses in form input in listbox
these three months, and I will choose july for example. Now in form input an
only one month - «july» will appear in listbox. And I need all of variants
months on 1977 year. How to take them all avaliable criteria from the list of
Autofilter?

How to get this?
In fact Excel it as did do that?
Help me please in it :(
Thank you

You can add an auxilary column to the worksheet which contains years. The
column can be hidden or put it in a coulumn that is not normally seen (IV).

"Valery" wrote:

Hi all!
It seems to me that my last question about criteria wasn't considered :(
I read this http://www.j-walk.com/ss/excel/usertips/tip044.htm but it's not
That I'll now one more try explain my problem
link to file http://aljany.com/Criterias_.jpg and my example file is here
http://aljany.com/My_Book.xls
The first column contains years, the second contains month. On these columns
Autofilter is applied. There is a form of input of filter for users. In it
we choose a year, for example 1977. Excel will filter on a year the first
column and will rotin in Autofilter in the second column collection from
months, for example «august, september, june» are avaliable. How to get this
avaliable criteria values? If the program chooses in form input in listbox
these three months, and I will choose july for example. Now in form input an
only one month - «july» will appear in listbox. And I need all of variants
months on 1977 year. How to take them all avaliable criteria from the list of
Autofilter?

How to get this?
In fact Excel it as did do that?
Help me please in it :(
Thank you

But i need selected all values to any cells or to any variables, from
criteria list of autofilter.
It's possible? How to get this list?
exel this list is present!
How manually
"Joel" wrote:

You can add an auxilary column to the worksheet which contains years. The
column can be hidden or put it in a coulumn that is not normally seen (IV).

"Valery" wrote:

Hi all!
It seems to me that my last question about criteria wasn't considered :(
I read this http://www.j-walk.com/ss/excel/usertips/tip044.htm but it's not
That I'll now one more try explain my problem
link to file http://aljany.com/Criterias_.jpg and my example file is here
http://aljany.com/My_Book.xls
The first column contains years, the second contains month. On these columns
Autofilter is applied. There is a form of input of filter for users. In it
we choose a year, for example 1977. Excel will filter on a year the first
column and will rotin in Autofilter in the second column collection from
months, for example «august, september, june» are avaliable. How to get this
avaliable criteria values? If the program chooses in form input in listbox
these three months, and I will choose july for example. Now in form input an
only one month - «july» will appear in listbox. And I need all of variants
months on 1977 year. How to take them all avaliable criteria from the list of
Autofilter?

How to get this?
In fact Excel it as did do that?
Help me please in it :(
Thank you

Why not just produce a list of unique values using Advanced Filter?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Valery" wrote in message
...
But i need selected all values to any cells or to any variables, from
criteria list of autofilter.
It's possible? How to get this list?
exel this list is present!
How manually
"Joel" wrote:

You can add an auxilary column to the worksheet which contains years.
The
column can be hidden or put it in a coulumn that is not normally seen
(IV).

"Valery" wrote:

Hi all!
It seems to me that my last question about criteria wasn't considered
:(
I read this http://www.j-walk.com/ss/excel/usertips/tip044.htm but
it's not
That I'll now one more try explain my problem
link to file http://aljany.com/Criterias_.jpg and my example file is
here
http://aljany.com/My_Book.xls
The first column contains years, the second contains month. On these
columns
Autofilter is applied. There is a form of input of filter for users.
In it
we choose a year, for example 1977. Excel will filter on a year the
first
column and will rotin in Autofilter in the second column collection
from
months, for example «august, september, june» are avaliable. How to get
this
avaliable criteria values? If the program chooses in form input in
listbox
these three months, and I will choose july for example. Now in form
input an
only one month - «july» will appear in listbox. And I need all of
variants
months on 1977 year. How to take them all avaliable criteria from the
list of
Autofilter?

How to get this?
In fact Excel it as did do that?
Help me please in it :(
Thank you

How do it manually via VBA code?

"Valery" wrote:

But i need selected all values to any cells or to any variables, from
criteria list of autofilter.
It's possible? How to get this list?
exel this list is present!
How manually
"Joel" wrote:

You can add an auxilary column to the worksheet which contains years. The
column can be hidden or put it in a coulumn that is not normally seen (IV).

"Valery" wrote:

Hi all!
It seems to me that my last question about criteria wasn't considered :(
I read this http://www.j-walk.com/ss/excel/usertips/tip044.htm but it's not
That I'll now one more try explain my problem
link to file http://aljany.com/Criterias_.jpg and my example file is here
http://aljany.com/My_Book.xls
The first column contains years, the second contains month. On these columns
Autofilter is applied. There is a form of input of filter for users. In it
we choose a year, for example 1977. Excel will filter on a year the first
column and will rotin in Autofilter in the second column collection from
months, for example «august, september, june» are avaliable. How to get this
avaliable criteria values? If the program chooses in form input in listbox
these three months, and I will choose july for example. Now in form input an
only one month - «july» will appear in listbox. And I need all of variants
months on 1977 year. How to take them all avaliable criteria from the list of
Autofilter?

How to get this?
In fact Excel it as did do that?
Help me please in it :(
Thank you

Once you get the years column then use offset to get back to the column you
really need. I usually don't use autofilter and just write code to select
the item myself.

First = true
for RowCount = 1 to 100
if year(Range("A" & RowCount)) = 1977) then
if first = true
set MyRange = Range("A" & RowCount)
First = False
else
set MyRange = Union(MyRange,Range("A" & RowCount))
end if
next RowCount
MyRange.Select

"Valery" wrote:

But i need selected all values to any cells or to any variables, from
criteria list of autofilter.
It's possible? How to get this list?
exel this list is present!
How manually
"Joel" wrote:

You can add an auxilary column to the worksheet which contains years. The
column can be hidden or put it in a coulumn that is not normally seen (IV).

"Valery" wrote:

Hi all!
It seems to me that my last question about criteria wasn't considered :(
I read this http://www.j-walk.com/ss/excel/usertips/tip044.htm but it's not
That I'll now one more try explain my problem
link to file http://aljany.com/Criterias_.jpg and my example file is here
http://aljany.com/My_Book.xls
The first column contains years, the second contains month. On these columns
Autofilter is applied. There is a form of input of filter for users. In it
we choose a year, for example 1977. Excel will filter on a year the first
column and will rotin in Autofilter in the second column collection from
months, for example «august, september, june» are avaliable. How to get this
avaliable criteria values? If the program chooses in form input in listbox
these three months, and I will choose july for example. Now in form input an
only one month - «july» will appear in listbox. And I need all of variants
months on 1977 year. How to take them all avaliable criteria from the list of
Autofilter?

How to get this?
In fact Excel it as did do that?
Help me please in it :(
Thank you

Just to add to Bob's response...

Debra Dalgleish shows how to do this advanced filter:
http://contextures.com/xladvfilter01.html#FilterUR


Bob Phillips wrote:

Why not just produce a list of unique values using Advanced Filter?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Valery" wrote in message
...
But i need selected all values to any cells or to any variables, from
criteria list of autofilter.
It's possible? How to get this list?
exel this list is present!
How manually
"Joel" wrote:

You can add an auxilary column to the worksheet which contains years.
The
column can be hidden or put it in a coulumn that is not normally seen
(IV).

"Valery" wrote:

Hi all!
It seems to me that my last question about criteria wasn't considered
:(
I read this http://www.j-walk.com/ss/excel/usertips/tip044.htm but
it's not
That I'll now one more try explain my problem
link to file http://aljany.com/Criterias_.jpg and my example file is
here
http://aljany.com/My_Book.xls
The first column contains years, the second contains month. On these
columns
Autofilter is applied. There is a form of input of filter for users.
In it
we choose a year, for example 1977. Excel will filter on a year the
first
column and will rotin in Autofilter in the second column collection
from
months, for example «august, september, june» are avaliable. How to get
this
avaliable criteria values? If the program chooses in form input in
listbox
these three months, and I will choose july for example. Now in form
input an
only one month - «july» will appear in listbox. And I need all of
variants
months on 1977 year. How to take them all avaliable criteria from the
list of
Autofilter?

How to get this?
In fact Excel it as did do that?
Help me please in it :(
Thank you

--

Dave Peterson

If you have:
Column A ColumnB
1970 Jan
1971 Mar
1975 Feb
1977 Jul
1972 Oct
1970 Jul
1971 Aug
1977 Dec
1975 Jan
1973 Apr
1977 Nov

And you set your criteria as:
column A
1977

It should return in the designated range:
Jul
Dec
Nov

Is this what you mean?

"Valery" wrote:

Hi all!
It seems to me that my last question about criteria wasn't considered :(
I read this http://www.j-walk.com/ss/excel/usertips/tip044.htm but it's not
That I'll now one more try explain my problem
link to file http://aljany.com/Criterias_.jpg and my example file is here
http://aljany.com/My_Book.xls
The first column contains years, the second contains month. On these columns
Autofilter is applied. There is a form of input of filter for users. In it
we choose a year, for example 1977. Excel will filter on a year the first
column and will rotin in Autofilter in the second column collection from
months, for example «august, september, june» are avaliable. How to get this
avaliable criteria values? If the program chooses in form input in listbox
these three months, and I will choose july for example. Now in form input an
only one month - «july» will appear in listbox. And I need all of variants
months on 1977 year. How to take them all avaliable criteria from the list of
Autofilter?

How to get this?
In fact Excel it as did do that?
Help me please in it :(
Thank you

Hello.
The file http://aljany.com/My_Book.xls applied by me is only a small example
Actually at me there is a greater database from 2000 records in 120
autofilter fields in every record. Therefore initially also there was such
question:
How to get (fast get) this list?
http://aljany.com/Criterias2_.jpg
Here is this file base http://aljany.com/bigbase.xls
Therefore I also had a question as it was possible to get fast without loop
exel present list all avaliable value in autofilter. How read this values?
But the exel stores these list elements somewhere, doesn't it?
Help me please :(
Thanks for you time

"JLGWhiz" wrote:

If you have:
Column A ColumnB
1970 Jan
1971 Mar
1975 Feb
1977 Jul
1972 Oct
1970 Jul
1971 Aug
1977 Dec
1975 Jan
1973 Apr
1977 Nov

And you set your criteria as:
column A
1977

It should return in the designated range:
Jul
Dec
Nov

Is this what you mean?

"Valery" wrote:

Hi all!
It seems to me that my last question about criteria wasn't considered :(
I read this http://www.j-walk.com/ss/excel/usertips/tip044.htm but it's not
That I'll now one more try explain my problem
link to file http://aljany.com/Criterias_.jpg and my example file is here
http://aljany.com/My_Book.xls
The first column contains years, the second contains month. On these columns
Autofilter is applied. There is a form of input of filter for users. In it
we choose a year, for example 1977. Excel will filter on a year the first
column and will rotin in Autofilter in the second column collection from
months, for example «august, september, june» are avaliable. How to get this
avaliable criteria values? If the program chooses in form input in listbox
these three months, and I will choose july for example. Now in form input an
only one month - «july» will appear in listbox. And I need all of variants
months on 1977 year. How to take them all avaliable criteria from the list of
Autofilter?

How to get this?
In fact Excel it as did do that?
Help me please in it :(
Thank you

AutoFilter is the fastest way to extract the list using the criteria. You
can use the AdvancedFilter method to filter and simultaneously copy the list
to another location.
I am truly having difficulty understanding exactly what you are asking, but
after looking at your file, it appears that you are setting up the auto
filter properly. So, I assume you are questioning whether there is a faster
method to do the job. My answer would be that I don't think there is a
faster method.

"Valery" wrote:

Hello.
The file http://aljany.com/My_Book.xls applied by me is only a small example
Actually at me there is a greater database from 2000 records in 120
autofilter fields in every record. Therefore initially also there was such
question:
How to get (fast get) this list?
http://aljany.com/Criterias2_.jpg
Here is this file base http://aljany.com/bigbase.xls
Therefore I also had a question as it was possible to get fast without loop
exel present list all avaliable value in autofilter. How read this values?
But the exel stores these list elements somewhere, doesn't it?
Help me please :(
Thanks for you time

"JLGWhiz" wrote:

If you have:
Column A ColumnB
1970 Jan
1971 Mar
1975 Feb
1977 Jul
1972 Oct
1970 Jul
1971 Aug
1977 Dec
1975 Jan
1973 Apr
1977 Nov

And you set your criteria as:
column A
1977

It should return in the designated range:
Jul
Dec
Nov

Is this what you mean?

"Valery" wrote:

Hi all!
It seems to me that my last question about criteria wasn't considered :(
I read this http://www.j-walk.com/ss/excel/usertips/tip044.htm but it's not
That I'll now one more try explain my problem
link to file http://aljany.com/Criterias_.jpg and my example file is here
http://aljany.com/My_Book.xls
The first column contains years, the second contains month. On these columns
Autofilter is applied. There is a form of input of filter for users. In it
we choose a year, for example 1977. Excel will filter on a year the first
column and will rotin in Autofilter in the second column collection from
months, for example «august, september, june» are avaliable. How to get this
avaliable criteria values? If the program chooses in form input in listbox
these three months, and I will choose july for example. Now in form input an
only one month - «july» will appear in listbox. And I need all of variants
months on 1977 year. How to take them all avaliable criteria from the list of
Autofilter?

How to get this?
In fact Excel it as did do that?
Help me please in it :(
Thank you

Hello JLGWhiz!
You wrote:
"AutoFilter is the fastest way to extract the list using the criteria. You
can use the AdvancedFilter method to filter and simultaneously copy the list
to another location."
Which copy list? list of result or list of avaliable criteria?
how to get the list of all criterias witch avaliable present in autofilter
DropDown menu?

"JLGWhiz" wrote:

AutoFilter is the fastest way to extract the list using the criteria. You
can use the AdvancedFilter method to filter and simultaneously copy the list
to another location.
I am truly having difficulty understanding exactly what you are asking, but
after looking at your file, it appears that you are setting up the auto
filter properly. So, I assume you are questioning whether there is a faster
method to do the job. My answer would be that I don't think there is a
faster method.

"Valery" wrote:

Hello.
The file http://aljany.com/My_Book.xls applied by me is only a small example
Actually at me there is a greater database from 2000 records in 120
autofilter fields in every record. Therefore initially also there was such
question:
How to get (fast get) this list?
http://aljany.com/Criterias2_.jpg
Here is this file base http://aljany.com/bigbase.xls
Therefore I also had a question as it was possible to get fast without loop
exel present list all avaliable value in autofilter. How read this values?
But the exel stores these list elements somewhere, doesn't it?
Help me please :(
Thanks for you time

"JLGWhiz" wrote:

If you have:
Column A ColumnB
1970 Jan
1971 Mar
1975 Feb
1977 Jul
1972 Oct
1970 Jul
1971 Aug
1977 Dec
1975 Jan
1973 Apr
1977 Nov

And you set your criteria as:
column A
1977

It should return in the designated range:
Jul
Dec
Nov

Is this what you mean?

"Valery" wrote:

Hi all!
It seems to me that my last question about criteria wasn't considered :(
I read this http://www.j-walk.com/ss/excel/usertips/tip044.htm but it's not
That I'll now one more try explain my problem
link to file http://aljany.com/Criterias_.jpg and my example file is here
http://aljany.com/My_Book.xls
The first column contains years, the second contains month. On these columns
Autofilter is applied. There is a form of input of filter for users. In it
we choose a year, for example 1977. Excel will filter on a year the first
column and will rotin in Autofilter in the second column collection from
months, for example «august, september, june» are avaliable. How to get this
avaliable criteria values? If the program chooses in form input in listbox
these three months, and I will choose july for example. Now in form input an
only one month - «july» will appear in listbox. And I need all of variants
months on 1977 year. How to take them all avaliable criteria from the list of
Autofilter?

How to get this?
In fact Excel it as did do that?
Help me please in it :(
Thank you

You've started another thread - is my answer there what you want?

Pete

On Oct 9, 8:11 am, Valery wrote:
Hello JLGWhiz!You wrote:

"AutoFilter is the fastest way to extract the list using the criteria. You
can use the AdvancedFilter method to filter and simultaneously copy the list
to another location."
Which copy list? list of result or list of avaliable criteria?
how to get the list of all criterias witch avaliable present in autofilter
DropDown menu?



"JLGWhiz" wrote:
AutoFilter is the fastest way to extract the list using the criteria. You
can use the AdvancedFilter method to filter and simultaneously copy the list
to another location.
I am truly having difficulty understanding exactly what you are asking, but
after looking at your file, it appears that you are setting up the auto
filter properly. So, I assume you are questioning whether there is a faster
method to do the job. My answer would be that I don't think there is a
faster method.

"Valery" wrote:

Hello.
The filehttp://aljany.com/My_Book.xlsapplied by me is only a small example
Actually at me there is a greater database from 2000 records in 120
autofilter fields in every record. Therefore initially also there was such
question:
How to get (fast get) this list?
http://aljany.com/Criterias2_.jpg
Here is this file basehttp://aljany.com/bigbase.xls
Therefore I also had a question as it was possible to get fast without loop
exel present list all avaliable value in autofilter. How read this values?
But the exel stores these list elements somewhere, doesn't it?
Help me please :(
Thanks for you time

"JLGWhiz" wrote:

If you have:
Column A ColumnB
1970 Jan
1971 Mar
1975 Feb
1977 Jul
1972 Oct
1970 Jul
1971 Aug
1977 Dec
1975 Jan
1973 Apr
1977 Nov

And you set your criteria as:
column A
1977

It should return in the designated range:
Jul
Dec
Nov

Is this what you mean?

"Valery" wrote:

Hi all!
It seems to me that my last question about criteria wasn't considered :(
I read this http://www.j-walk.com/ss/excel/usertips/tip044.htmbut it's not
That I'll now one more try explain my problem
link to filehttp://aljany.com/Criterias_.jpgand my example file is here
http://aljany.com/My_Book.xls
The first column contains years, the second contains month. On these columns
Autofilter is applied. There is a form of input of filter for users. In it
we choose a year, for example 1977. Excel will filter on a year the first
column and will rotin in Autofilter in the second column collection from
months, for example «august, september, june» are avaliable. How to get this
avaliable criteria values? If the program chooses in form input in listbox
these three months, and I will choose july for example. Now in form input an
only one month - «july» will appear in listbox. And I need all of variants
months on 1977 year. How to take them all avaliable criteria from the list of
Autofilter?

How to get this?
In fact Excel it as did do that?
Help me please in it :(
Thank you- Hide quoted text -

- Show quoted text -

Thank you Pete
This them is Closed.
Sorry
"Pete_UK" wrote:

You've started another thread - is my answer there what you want?

Pete

On Oct 9, 8:11 am, Valery wrote:
Hello JLGWhiz!You wrote:

"AutoFilter is the fastest way to extract the list using the criteria. You
can use the AdvancedFilter method to filter and simultaneously copy the list
to another location."
Which copy list? list of result or list of avaliable criteria?
how to get the list of all criterias witch avaliable present in autofilter
DropDown menu?



"JLGWhiz" wrote:
AutoFilter is the fastest way to extract the list using the criteria. You
can use the AdvancedFilter method to filter and simultaneously copy the list
to another location.
I am truly having difficulty understanding exactly what you are asking, but
after looking at your file, it appears that you are setting up the auto
filter properly. So, I assume you are questioning whether there is a faster
method to do the job. My answer would be that I don't think there is a
faster method.

"Valery" wrote:

Hello.
The filehttp://aljany.com/My_Book.xlsapplied by me is only a small example
Actually at me there is a greater database from 2000 records in 120
autofilter fields in every record. Therefore initially also there was such
question:
How to get (fast get) this list?
http://aljany.com/Criterias2_.jpg
Here is this file basehttp://aljany.com/bigbase.xls
Therefore I also had a question as it was possible to get fast without loop
exel present list all avaliable value in autofilter. How read this values?
But the exel stores these list elements somewhere, doesn't it?
Help me please :(
Thanks for you time

"JLGWhiz" wrote:

If you have:
Column A ColumnB
1970 Jan
1971 Mar
1975 Feb
1977 Jul
1972 Oct
1970 Jul
1971 Aug
1977 Dec
1975 Jan
1973 Apr
1977 Nov

And you set your criteria as:
column A
1977

It should return in the designated range:
Jul
Dec
Nov

Is this what you mean?

"Valery" wrote:

Hi all!
It seems to me that my last question about criteria wasn't considered :(
I read this http://www.j-walk.com/ss/excel/usertips/tip044.htmbut it's not
That I'll now one more try explain my problem
link to filehttp://aljany.com/Criterias_.jpgand my example file is here
http://aljany.com/My_Book.xls
The first column contains years, the second contains month. On these columns
Autofilter is applied. There is a form of input of filter for users. In it
we choose a year, for example 1977. Excel will filter on a year the first
column and will rotin in Autofilter in the second column collection from
months, for example «august, september, june» are avaliable. How to get this
avaliable criteria values? If the program chooses in form input in listbox
these three months, and I will choose july for example. Now in form input an
only one month - «july» will appear in listbox. And I need all of variants
months on 1977 year. How to take them all avaliable criteria from the list of
Autofilter?

How to get this?
In fact Excel it as did do that?
Help me please in it :(
Thank you- Hide quoted text -

- Show quoted text -