Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
remembering the same filtering criteria
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
remembering the same filtering criteria
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
remembering the same filtering criteria
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
remembering the same filtering criteria
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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
remembering the same filtering criteria
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
remembering the same filtering criteria
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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
remembering the same filtering criteria
Sub Reset_FilterCriteria()
sFilterCriteria = "" End Sub Run the above when user wants to enter new criteria in A1. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
remembering the same filtering criteria
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
remembering the same filtering criteria
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
remembering the same filtering criteria
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
remembering the same filtering criteria
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 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[s]! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
remembering the same filtering criteria
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
remembering the same filtering criteria
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
remembering the same filtering criteria
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering by three criteria including top ten value | Excel Programming | |||
Excel "remembering" sort criteria | Excel Discussion (Misc queries) | |||
Autofilter Criteria not filtering when using NOW() | Excel Programming | |||
Filtering by criteria | Excel Programming | |||
Advanced Filtering criteria | Excel Programming |