filter & show filter

dantb

Active Member
Joined
Mar 20, 2002
Messages
358
Hi all, what I am trying to do is use a vba filter, and have it show what I filtered on in the above cell (a1). I found this code but can't seem to make it work. I put it in a modual, and on the filter sheet (sheet1),cell a1,I put =FilterCriteria A3
but it dont seem to work. Thanks for any help Dan

Here is the Modual Code:

Function FilterCriteria(Rng As Range) As String
Dim Filter As String
Filter = ""
On Error GoTo Finish
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 x1And
Filter = Filter & " AND " & .Criteria2
Case x1Or
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish
FilterCriteria = Filter
End Function
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
Hi dantb,

Please try to put as follows.
=FilterCriteria(A3)

And you need modify the code a little. Please note bold text.<PRE><FONT color=red>Function</FONT>FilterCriteria(Rng<FONT color=red>As</FONT> Range)<FONT color=red>As</FONT><FONT color=red> String</FONT><FONT color=red>Dim</FONT>Filter<FONT color=red>As</FONT><FONT color=red> String</FONT>

Filter = ""<FONT color=red>On Error</FONT><FONT color=red>GoTo</FONT> Finish<FONT color=red>With</FONT>Rng.Parent.AutoFilter<FONT color=red>If</FONT>Intersect(Rng, .Range) Is<FONT color=red> Nothing</FONT><FONT color=red> Then</FONT><FONT color=red>GoTo</FONT> Finish<FONT color=red>With</FONT>.Filters(Rng.Column - .Range.Column + 1)<FONT color=red>If</FONT>Not .On<FONT color=red> Then</FONT><FONT color=red>GoTo</FONT> Finish

Filter = .Criteria1

Select<FONT color=red>Case</FONT>.Operator<FONT color=red>Case</FONT>xlAnd<FONT color=#339966>'//Not X1</FONT>
Filter = Filter & " AND " & .Criteria2<FONT color=red>Case</FONT>xlOr<FONT color=#339966>'//Same sa above</FONT>
Filter = Filter & " OR " & .Criteria2<FONT color=red>End Select</FONT><FONT color=red>End With</FONT><FONT color=red>End With</FONT>

Finish:

FilterCriteria = Filter<FONT color=red>End Function</FONT></PRE>


_________________
HTH

With Regards,
Colo

This message was edited by Colo on 2002-10-30 20:59
This message was edited by Colo on 2002-10-30 21:00
This message was edited by Colo on 2002-10-30 21:01
 

dantb

Active Member
Joined
Mar 20, 2002
Messages
358
Thank you so much Colo, works great, just what I was looking for. Thanks again & have a good one Dan !.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,575
Messages
5,596,961
Members
414,114
Latest member
Lost_User21

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