Filter Detail Display

  • Thread starter Thread starter steve greenwood
  • Start date Start date
S

steve greenwood

We use autofilter extensively so that we can produce generic lists but
allow users to tailor the lists as required. Unfortunately this can
lead to misunderstandings when different filter criteria are applied to
the same list.

How can we display the current criteria while looking at / printing the
data itself ?
 
Steve,

Use this VB routine and in your filterarea (e.g. above the row autofilters:
let's say A2:D2) take up the formula : = Filtercriteria(A2) ;
=Filtercriteria(B2) etc.
Whenever you than change the "settings" of a filter, the content of it is
given in the row above it.

( I did program this my self, but I found it an excellent tool. There is
however one minor "bug" in it : If you "clean" the filter via the menu :
Data / Filter / Show All the line containing this function isn't cleared as
well. F9 however copes with that).


Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
Application.Volatile
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 xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Where I said I did program it myself I meant to say : I dit NOT program it
myself. !!

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

A.W.J. Ales said:
Steve,

Use this VB routine and in your filterarea (e.g. above the row autofilters:
let's say A2:D2) take up the formula : = Filtercriteria(A2) ;
=Filtercriteria(B2) etc.
Whenever you than change the "settings" of a filter, the content of it is
given in the row above it.

( I did program this my self, but I found it an excellent tool. There is
however one minor "bug" in it : If you "clean" the filter via the menu :
Data / Filter / Show All the line containing this function isn't cleared as
well. F9 however copes with that).


Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
Application.Volatile
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 xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
Click to expand...
 
Back
Top