Hi,
I'm very much with Dave here.... this may help...
The following code is from ...
http://www.mrexcel.com/forum/excel-questions/107808-autofilter-showing-criteria.html
Google string "reading filters in excel vba"
It shows that it's possible to interrogate the Filters collection.
HTH
Lisa
Copy and pasted "as is"....
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
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
'By Stephen Bullen
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
-----Original Message-----
From: 'David Smart' smartware.consulting@gmail.com [ExcelVBA] <ExcelVBA@yahoogroups.com>
To: ExcelVBA <ExcelVBA@yahoogroups.com>
Sent: Wed, Jun 10, 2015 3:30 am
Subject: Re: [ExcelVBA] Re: Auto Filter Help
From: 'David Smart' smartware.consulting@gmail.com [ExcelVBA] <ExcelVBA@yahoogroups.com>
To: ExcelVBA <ExcelVBA@yahoogroups.com>
Sent: Wed, Jun 10, 2015 3:30 am
Subject: Re: [ExcelVBA] Re: Auto Filter Help
Regards, Dave S
It's easy enough to turn autofilters off and then on again. This will at least save some work.
What will take the effort is capturing and reimposing the criteria active at the time. However, I do think it's doable in VBA, if you have a bit of time to spend on it.
Regards, Dave S
----- Original Message -----Sent: Wednesday, June 10, 2015 10:43 AMSubject: [ExcelVBA] Re: Auto Filter HelpWell, I appreciate the help everyone, but it may be impossible in VBA. I'll keep searching the net in hopes of finding something. It's not a big deal, the filter I usually have on is for two columns, and it's not TOO big a deal to have to turn them back on afterwards, just breaks the flow of data entry.
ScottNo virus found in this message.
Checked by AVG - www.avg.com
Version: 2015.0.5961 / Virus Database: 4355/9985 - Release Date: 06/09/15
__._,_.___
Posted by: Green <1z@compuserve.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (8) |
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
.
__,_._,___
Tidak ada komentar:
Posting Komentar