Prev: Can't create an Object (Some sort of ActiveX thing?)
Next: How to use Excel to determine cutting and reusing stock to fill or
From: emilyyy on 4 Sep 2009 19:31
I have a sheet with autofilter. I need to turn off the autofilter
before a macro runs but then I need to return it to the original
condition (i.e. only original filtered rows are shown). How can I
record the autofilter criteria and the field number in order to
restore the original selected autofilter?
Thanks very much in advance for any help!
From: JLatham on 4 Sep 2009 21:33
There's some pretty good information about AutoFilter here:
combine that with what you can find in the VB Help on AutoFilter and you end
up building something like this:
Dim filterState As Boolean
Dim filterRangeAddress As String
Dim fc As Long ' to work through possible filter fields
Dim filterField As Long
Dim filterCriteria1 As Variant
If ActiveSheet.FilterMode Then
'remember that .FilterMode is true
filterState = True
For fc = 1 To .AutoFilter.Filters.Count
If .AutoFilter.Filters(fc).On Then
filterField = fc
filterRangeAddress = .AutoFilter.Range.Address
filterCriteria1 = .AutoFilter.Filters(filterField).Criteria1
'now show all data; turns .FilterMode off
MsgBox "Do other stuff here while all data is visible"
'now we set things back the way they were
If filterState Then
> I have a sheet with autofilter. I need to turn off the autofilter
> before a macro runs but then I need to return it to the original
> condition (i.e. only original filtered rows are shown). How can I
> record the autofilter criteria and the field number in order to
> restore the original selected autofilter?
> Thanks very much in advance for any help!
> Best Regards,