remembering the same filtering criteria

  • Thread starter Thread starter baha17
  • Start date Start date
B

baha17

Dear All,

I would like to know that is there any way of recording criteria when I filtered data. For example; A1,A2,A3,A4,A5,A6,A7,A8,A9,A10 and A11 I have this data such as Data,1,2,3,4,5,6,7,8,9,10. Now when I created auto filter as

ActiveSheet.Range("$A$2:$A$11").AutoFilter Field:=1, Criteria1:=Array("2", "3", "4", "5"), Operator:=xlFilterValues

And this criteria can be changed by the user anytime. Now for some reason sometimes I trigger a code which works under Function key(F8) and it refresh some data from the database. Just before it gets the data, it will show all data as :

ActiveSheet.Range("$A$1:$A$11").AutoFilter Field:=1

My problem is I want excel to remember criteria after I get the data from database and filter as I previously did. Is there anyway of doing it?

Thank you very much for the help
Baha
 
Dear All,
I would like to know that is there any way of recording criteria when
I filtered data. For example; A1,A2,A3,A4,A5,A6,A7,A8,A9,A10 and A11
I have this data such as Data,1,2,3,4,5,6,7,8,9,10. Now when I
created auto filter as

ActiveSheet.Range("$A$2:$A$11").AutoFilter Field:=1,
Criteria1:=Array("2", "3", "4", "5"), Operator:=xlFilterValues

And this criteria can be changed by the user anytime. Now for some
reason sometimes I trigger a code which works under Function key(F8)
and it refresh some data from the database. Just before it gets the
data, it will show all data as :

ActiveSheet.Range("$A$1:$A$11").AutoFilter Field:=1

My problem is I want excel to remember criteria after I get the data
from database and filter as I previously did. Is there anyway of
doing it?

Thank you very much for the help
Baha
Click to expand...

Why not store the criteria in a cell, OR a VBA global variable so it
holds the value for the life of runtime or until a user changes it.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Gary,
This is exactly what I need,but I dont know how I can I do that?Any help to go through that?
Thanks
Baha
 
Hi Gary,
This is exactly what I need,but I dont know how I can I do that?Any
help to go through that? Thanks
Baha
Click to expand...

You can't use an array as Criteria; it has to be a string.

You can store a criteria in a cell (say A1) and pull it into your code
like this...

With Range("$A$2:$A$11")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=Range("A1").Value
End With

Here's a routine that takes a single criteria stored in A1 and sets
autofilter...

Sub Set_AutoFilter()
With ActiveSheet
If .AutoFilterMode Then .AutoFilterMode = False
With .Range("$A$2:$A$11")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=Range("A1").Value
End With '.Range("$A$2:$A$11")
End With 'ActiveSheet
End Sub

...where you'll need to change the range ref if not using A1 to store
the user criteria.

I tried doing a custom criteria but it didn't work as per the
recommended syntax. I even tried playing back the macro recorder code
(same as recommended syntax) and it didn't work either. Go figure!!!
Hopefully someone who actually uses both criteria in code will chime
in!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi GS,

Unfortunately that was not what I wanted. I know that you can set the criteria from a cell(A1). But what I want to store a filtered criteria in one cell(A1) then I remove the filter.After that I run another macro then I want to put back my filter with the same criteria as in Range A1.
Thanks for your help
Baha
 
Hi GS,
Unfortunately that was not what I wanted. I know that you can set the
criteria from a cell(A1). But what I want to store a filtered
criteria in one cell(A1) then I remove the filter.After that I run
another macro then I want to put back my filter with the same
criteria as in Range A1. Thanks for your help Baha
Click to expand...

You can store the criteria in a variable during runtime, and access it
anytime...

In a standard module:
Public sFilterCriteria$ '//stores last used criteria


Sub Set_AutoFilter()
If sCriteria = "" Then sCriteria = Range("A1").Value
With ActiveSheet
If .AutoFilterMode Then .AutoFilterMode = False
With .Range("$A$2:$A$11")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=sFilterCriteria
End With '.Range("$A$2:$A$11")
End With 'ActiveSheet
End Sub

Sub Reset_FilterCriteria()
sFilterCriteria = ""
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi CS,
Thanks for your help upto now.However that part I am still missing.How can I store the criteria in a variable during runtime? Lets say that sFilterCriteria, how to set the value when i filter?I am assuming it should work something like if activesheet.autofiltermode then ... that part i do notbnow the code.
By the way i can do all these by userform but I think it is better the way you are telling me
Thanks for your afford
 
Hi CS,
Thanks for your help upto now.However that part I am still missing.How can I store the criteria in a variable during runtime? Lets say that sFilterCriteria, how to set the value when i filter?I am assuming it should work something like if activesheet.autofiltermode then ... that part i do notbnow the code.
By the way i can do all these by userform but I think it is better the way you are telling me
Thanks for your afford
 
Hi CS,
Thanks for your help upto now.However that part I am still missing.How can I store the criteria in a variable during runtime? Lets say that sFilterCriteria, how to set the value when i filter?I am assuming it should work something like if activesheet.autofiltermode then ... that part i do notbnow the code.
By the way i can do all these by userform but I think it is better the way you are telling me
Thanks for your afford
 
Hi CS,
Thanks for your help upto now.However that part I am still
missing.How can I store the criteria in a variable during runtime?
Lets say that sFilterCriteria, how to set the value when i filter?I
am assuming it should work something like if
activesheet.autofiltermode then ... that part i do notbnow the code.
By the way i can do all these by userform but I think it is better
the way you are telling me Thanks for your afford
Click to expand...

I showed you how to impliment it AND gave you a mechanism for resetting
it! Clearly you did not try it out because it tested perfectly when I
ran the code.

There's no reason the solution I proposed won't work with a userform or
worksheet button!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
hi,

Dim w As Worksheet
Dim filterArray()
Dim currentFiltRange As String

Sub Store_and_clear_Filters()
Set w = ActiveSheet
With w.AutoFilter
currentFiltRange = .Range.Address
With .Filters
ReDim filterArray(1 To .Count, 1 To 3)
For f = 1 To .Count
With .Item(f)
If .On Then
filterArray(f, 1) = .Criteria1
If .Operator Then
filterArray(f, 2) = .Operator
' filterArray(f, 3) = .Criteria2
End If
End If
End With
Next
End With
End With
w.AutoFilterMode = False
RestoreFilters

End Sub

Sub RestoreFilters()
For col = 1 To UBound(filterArray(), 1)
If Not IsEmpty(filterArray(col, 1)) Then
If filterArray(col, 2) Then
w.Range(currentFiltRange).AutoFilter field:=col, _
Criteria1:=filterArray(col, 1), _
Operator:=filterArray(col, 2)
Else
w.Range(currentFiltRange).AutoFilter field:=col, _
Criteria1:=filterArray(col, 1)
End If
End If
Next
End Sub

isabelle
 
Hi Isabella It worked like a charm.Perfect.way above my level though:)I think I could not undestand GS well but that code could not work for me for some reason.But it is ok, the second code is working very well.
Thank you very much for both of you

Baha
 
Hi Isabella,

Is that possible to set n number of field to that filtering are.So far the codes working very well selecting only one field,I have some issues with selecting multiple fields. I mean instead of only column A, what if I place some other data on column C,D,E,F...and filter all of them.Is that still possible to remember the cafeterias?
Thank you for the help
Baha
 
Back
Top