What is the value of AutoFilter ?

[Bruno_x]

I am trying to know what AutoFilter is used in Column B. I want to use this "value" to make a selection in a list of contact persons (cell B3) in a fax-sheet.

Regards,
Bruno

MrExcel - selectie leverancier.xls
	A	B	C	D	E	F	G
1
2	Supplier
3	Attn.
4
5
6	Ref	Suppl	Date	Weight
7	A15	AMEX	1/okt	1500
8	D16	AMEX	3/okt	9820
9	D18	AMEX	8/okt	7800
10	K4	BOEST	2/okt	5110
11	G9	BOEST	20/okt	3260
12
Blad1

 

[VoG]

I'm not sure if this is quite what you are after.

=FILTERCRIT(B3)

will return the filter criteria into a worksheet cell.

Function FilterCrit(Rng As Range) As String
Dim Filter As String
Filter = " "
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCrit = Right(Filter, Len(Filter) - 1)
End Function

 

[Bruno_x]

Hi VoG™
Yes, this is for 95% what i need, only
when i change the autofilter from amex to boest, the value in cell B2 (=filtercrit(b6) ) is still amex ...

 

[VoG]

Bruno

I'm not sure if B3 is the correct cell to use -- it needs to be a cell within the filtered range. Looking again at your worksheet maybe FILTERCRIT(B7) would be better.

I use this function myself and when the filter is changed it updates correctly (for me).

 

[Bruno_x]

VoG™
I've changed The formula in B2 to FILTERCRIT(B7) instead of FILTERCRIT(B6)
and now it works fine.
Thank again,