CODEWITHSUNDEEP
excelrefreshautofilter
elpablo
elpablo

Reputation: 51

Automatically refresh autofilter - VBA or not

I have been trying and failing to find a code that refreshes the auto-filter (criteria for filter is in column A) whenever new data is entered into column C (which is the root variable for values in A,B & D)

Any help would be MUCH appreciated.

Here is the code that I have found in other threads, but I can't get it to work. 

Private Sub Worksheet_Calculate() 

           If Me.FilterMode = True Then 
    With Application 
        .EnableEvents = False 
        .ScreenUpdating = False 
    End With 

    With ActiveWorkbook 
        .CustomViews.Add ViewName:="Mine", RowColSettings:=True 
        Me.AutoFilterMode = False 
        .CustomViews("Mine").Show 
        .CustomViews("Mine").Delete 
    End With 


    With Application 
        .EnableEvents = True 
        .ScreenUpdating = True 
    End With 
End If 
End Sub

Upvotes: 1

Views: 27656

Answers (1)

mr.Reband
mr.Reband

Reputation: 2430

This code needs to go in the module specific to the sheet that contains your AutoFilter: 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Then
        ActiveSheet.AutoFilter.ApplyFilter
    End If
End Sub

It should be noted that with this approach, you will only ever see rows for which the AutoFilter evaluates to true, so really, you only have the possibility of changing data in C such that rows that currently exist get removed after the filter refreshes, and not the other way around.

Hope that makes sense. Let me know if it doesn't.

Upvotes: 3

Related Questions