What is the value of AutoFilter ?

Bruno

Active Member
Joined
Feb 17, 2002
Messages
491
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
ABCDEFG
1
2Supplier
3Attn.
4
5
6RefSupplDateWeight
7A15AMEX1/okt1500
8D16AMEX3/okt9820
9D18AMEX8/okt7800
10K4BOEST2/okt5110
11G9BOEST20/okt3260
12
Blad1
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Active Member
Joined
Feb 17, 2002
Messages
491
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

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Active Member
Joined
Feb 17, 2002
Messages
491
VoG™
I've changed The formula in B2 to FILTERCRIT(B7) instead of FILTERCRIT(B6)
and now it works fine.
Thank again,
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,040
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top