Conditional Autofilter

Question

I use this macro to reapply a filter every time a change is made to the data of a sheet:

Private Sub Worksheet_Change(ByVal Target As Range)

   Sheets("SHIFTS").AutoFilter.ApplyFilter

End Sub

I need the filter to be reapplied only when the data entry contains a specific character.

e.g. User can input values 1,2,3,1S,2S,3S

I need the filter to be reapplied only when the character "S" is in the entered value.

It would help to know what column contains the *1,2,3,1S,2S,3S* value(s) which I assume is at least one of the filter fields. Also, confirm that Sheets("SHIFTS") is the Worksheet_Change's worksheet. — , Dec 26 '18 at 01:58
The filter is applied at column A, but the data entered are in columns B to AG. Also Sheets("SHIFTS") is the Worksheet_Change's worksheet — Ragnar Nightwalker, Dec 26 '18 at 02:14
Correct me if I'm wrong but there is no point on reapplying a filter when a new value is introduced unless that value is in a column that may change the filter results; i.e. one of the filter fields. — , Dec 26 '18 at 02:18
Column A just counts if the row isn't blank and then filters the empty rows out — Ragnar Nightwalker, Dec 26 '18 at 02:19
Yes you are right. So I just need the filter to be reapplied only when a value at column A changes value from 0 to 1, but not when every other change is made (1 to 2, 2 to 3 etc.) — Ragnar Nightwalker, Dec 26 '18 at 02:23

score 0 · Answer 1 · answered Dec 26 '18 at 02:15

0

If your 1,2,3,1S,2S,3S are in column A then this should reapply the existing AutoFilter whenever an nS value is typed or pasted into column A.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A:A"), UsedRange) Is Nothing Then
        On Error GoTo bye
        Application.EnableEvents = False
        Dim t As Range
        For Each t In Intersect(Target, Range("A:A"), UsedRange)
            If CBool(InStr(1, t.Value, "s", vbTextCompare)) Then
                If AutoFilterMode Then _
                    AutoFilter.ApplyFilter
                Exit For
            End If
        Next t
    End If

bye:
    Application.EnableEvents = True
End Sub

If all operations are performed on the Worksheet_Change's worksheet, parent worksheet references are automatically implicit on a worksheet's private code sheet.

answered Dec 26 '18 at 02:15

@RagnarNightwalker, This works for values with an *S* typed or pasted into column A. That was the original question. I'm unclear on how the recent comment about *'column A changes value from 0 to 1, but not when every other change is made (1 to 2, 2 to 3 etc.)'* fits into the original question. – Dec 26 '18 at 02:32
Thanks but this is not the case. I figured out the mistake so I now changed the formulas in column A to return only 0 and 1 depending on if the row is empty or not. So I just now need the macro to run only when there is a value change in column A, but not when there is an entry everywhere else in the sheet. – Ragnar Nightwalker Dec 26 '18 at 02:35
Sorry but I got confused. I turned a simple issue into a complex one for no reason. I'm relatively new to macro using in Excel. – Ragnar Nightwalker Dec 26 '18 at 02:38
OK, a change in the result of a formula does not trigger a worksheet_change. For that, you need to use worksheet_calculate with a list of values in column A 'remembered' from the previous calculation cycle and that would appear to be a completely different question. See [this](https://stackoverflow.com/questions/53737344/put-timestamp-when-formula-changes/53737792?r=SearchResults&s=4|0.0000#53737792). – Dec 26 '18 at 02:44
Thanks for your replies and for your time. – Ragnar Nightwalker Dec 26 '18 at 02:59

Conditional Autofilter

1 Answers1