how to work out what the auto fiter is

[smiler44]

I have a spread sheet that requires me to unfilter it, do some odds and sods and then rapply the filter.

Please can you help me with a macro/solution to work out what the filter criteria has been set to before I unfilter the spread sheet?

I don't even know where to start

thanks
smiler44

 

[smiler44]

wow I think I found an answer of sorts on the internet. this does not tell me what the filter is set to...well it may if I play with it but it stores the filter criteria and then re-applies the filter

 Sub ReDoAutoFilter()
    Dim w As Worksheet
    Dim filterArray()
    Dim currentFiltRange As String
    Dim col As Integer
     Set w = ActiveSheet
     ' Capture AutoFilter settings
    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 'simply delete this line to make it work in Excel 2010
                        End If
                    End If
                End With
            Next f
        End With
    End With
     'Remove AutoFilter
    w.AutoFilterMode = False
     ' Your code here
    MsgBox (" what has happened")
     ' Restore Filter settings
    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), _
                Criteria2:=filterArray(col, 3)
            Else
                w.Range(currentFiltRange).AutoFilter field:=col, _
                Criteria1:=filterArray(col, 1)
            End If
        End If
    Next col
End Sub

 

[smiler44]

the code in post 2 works if only 1 or 2 things had been selected by the filter but fails if 3 things were selected. i'll have a play tomorrow to see what needs changing

smiler44

 

[Rx_]

Explain the process to me.
1. Take off filter
2. Making changes to the underlying data
3. Reapplying the filter
OR
1. Changing how existing data should be filtered
2. Reapplying the filter
In other words, looking at the same data, with a different set of sunglasses to filter it

Example:
Expiration Date per item - filter expired 30 days out
The data column Days to expire could be recalculated each day then have the filter reapplied.
Or, the Expiration Date could remain constant - and the filter could be reapplied to Now(). No data changes, the filter is reapplied.

 

[smiler44]

Explain the process to me.
1. Take off filter
2. Making changes to the underlying data
3. Reapplying the filter
OR
1. Changing how existing data should be filtered
2. Reapplying the filter
In other words, looking at the same data, with a different set of sunglasses to filter it

Example:
Expiration Date per item - filter expired 30 days out
The data column Days to expire could be recalculated each day then have the filter reapplied.
Or, the Expiration Date could remain constant - and the filter could be reapplied to Now(). No data changes, the filter is reapplied.

Rx-
1. Take off filter
2. Making changes to the underlying data
3. Reapplying the filter

I think the code I found might work for me. If the filter is set to only show 1 or 2 things it worked. when the filter was set to show 3 items it failed. I think it may just need some tweeking to be able to allow me to have already filtered for 3 or more items but I'll take any help offered.

smiler44

 

[Gasman]

Are you sure it works only for up to 3 filters?

I've just tried it on a test sheet and put in 4 filter options and it works fine ?

Variable f is not defined also. Did you Dim that variable yourself.?

 

[Rx_]

OK, other than some tweeks, I think you got it.
Depending on the situation....
One of the things I do is to leave the data the same.
Then, in some hidden right hand columns create the metadata that represents the change.
The filters then use the meta data in the hidden fields.
That way the data remains intact, but is analyzed based on external evolving factors.
I call it a Wave Algorithm based off some old Cold War technology tricks.
Just google: wave algorithm excel vba "Rx_"

 

[smiler44]

ok RX_ thanks. I remed the line where it mentions about 2010 and it worked with more than 2 filters. I have Excel 2007 and so had left it in.
Forums, the internet, fantastic when used appropriately

smiler44