AutoFilter Criteria:=? Operator:=?

[Debraj]

Hi Excelian's

Is this possible to read (and store in a variant) what criteria / operator has been used in a Autofiltered range.


When I received the sheet, in many columns, different filter's are already applied.

Somewhere top 10, somewhere Filter by color, and somewhere month = "Jan". After Ctrl+Shift+L , I am unable to decide which filter was applied previously.


Can I able to

if cells.HasAutofilter("expected UDF"),then store the status,

unfilter all the data(Ctrl+Shift+L),

if cells.hasformula then paste as value

then rollback all the filter in its cells.autofilter PreviousStatus.


Thanks in advance.

Deb

 

[kchiba]

Hi Debra,


Check ou the following and see if you can adapt it to suit your purpose


http://www.teachexcel.com/free-excel-macros/m-137,udf-display-auto-filter-criteria-excel-free-macro.html

 

[Debraj]

@ kchiba,


Thank you for you help. above and below both example only works when YOU apply the condition, or condition were applied after the macro/function runs. If sheet has already conditions applied, it wouldn't work. It just tracing my activity.


http://datapigtechnologies.com/blog/index.php/show-autofilter-criteria-in-status-bar/


I have read both the post, but not able find a proper solution.


Please help.

 

[kchiba]

Hi Debraj,


I think it has to do with the fact that the macro is volatile and will showw the resutls on the bar any time there is a calculation on the sheet, however, I am not sure how you adapt it to give you a history of all prior filters.

 

[SirJB7]

Hi, Debraj Roy!

Try doing this:

a) insert a new module (VB editor (Alt-F11), Insert, Module)

b) type this code:

-----

[pre]

Sub X()

Dim I as Integer, A as String

With Worksheets("XXX")
If .AutoFilterMode Then
For I=1 to 1000
With .AutoFilter.Filters(I)
If .On Then
A = .Criteria1
B = .Criteria2
C = .Criteria3
D = .Criteria4
' follow as needed
Debug.Print "Filter: "; I, "Criteria 1:"; A, "Criteria 2:"; B, "Criteria 3:"; C, "Criteria 4:"; D,
Else
A = ""
B = ""
C = ""
D = ""
Exit For
End If
End With
Next I
End If
End With

End Sub

[/pre]
-----


This works with either a filter applied by yourself since you opened the workbook or for a pre-existent filter in the saved and opened workbook.


Regards!

 

[prasaddn]

Hi SirJB,


Good solution


Can something similar be done for the query in the topic below:

http://chandoo.org/forums/topic/getpivotdata-of-multiple-items-from-pivot-table-report-filter


The requirement is to show what selections have been made in Pivot Table page filter?


I had suggested two links, but not sure if those were having the right solution or not.


Regards,

Prasad DN

Edit: PS: Apologies for clubbing different topic under this thread..

 

[SirJB7]

Hi, prasaddn!


I saw the file and theoretically and accordingly to Excel documentation this should work:


-----

[pre]

With Worksheets("PivotTableP")
For J = 1 To .PivotTables.Count
With .PivotTables(J)
Debug.Print .ActiveFilters
End With
Next J
End With

[/pre]
-----


... but it doesn't: it raises an 450 error (incorrect arguments number -has no arguments!- or invalid property assignment -I'm retrieving, not assigning-).


Type the word ActiveFilters at any place in VB editor, click on it and press F1: there you'll have the sintax help for this property that simply states (unquoted) "expresion.ActiveFilter" with expression being a variable that represents a PT object (Worksheets("PivotTableP").PivotTables(1) is a PT object, isn't it?... property Name works...).


Regards!