Refresh Autofilter when data is added?

[HC1994]

Hi guys,

So I have a column, say column A, that I have a auto filter assigned. I would like it to auto-refresh the filter every time I add in data at the end.

I found this macro, but it only works for changes made within the filter, not if I add in data at the end.

http://www.ozgrid.com/forum/showthread.php?t=59917

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

Please let me know if you guys can help me out!

[Xx7]

This includes it in the Autofilter, however I'm assuming that you already have the column filtered and you want to maintain the same filter criteria after the addition. I will investigate how to store the prior criteria as an array.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    
        Dim c1 As Range 'First cell of filtered Range...
        Set c1 = Range("A8")
    
        Dim r1 As Long  'Last row of filtered Range...
        r1 = c1.End(xlDown).Row
    
        Selection.AutoFilter    'Unfiltered...
        ActiveSheet.Range(c1, Cells(r1, 1)).AutoFilter  'Filter new Range...
            

    End If

End Sub

[HC1994]

This includes it in the Autofilter, however I'm assuming that you already have the column filtered and you want to maintain the same filter criteria after the addition. I will investigate how to store the prior criteria as an array.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    
        Dim c1 As Range 'First cell of filtered Range...
        Set c1 = Range("A8")
    
        Dim r1 As Long  'Last row of filtered Range...
        r1 = c1.End(xlDown).Row
    
        Selection.AutoFilter    'Unfiltered...
        ActiveSheet.Range(c1, Cells(r1, 1)).AutoFilter  'Filter new Range...
            

    End If

End Sub

I checked yours and it worked, but it didn't on mine :/ I think it is because in between my rows there are spaces.

I have attached my file, please see what I am trying to do.
example.xlsx

Basically, I have these late trips, and I want to review them with my team in every weekly meeting. When I select a week at the top left corner(with filter), it will bring up a table that has all the information specific for that week.

Of course, this is a pretty brute-force way to do it, I don't know how to do it more elegantly though :/

[Xx7]

Yes, you are correct. Mine didn't include the spaces. See if this works for you.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    
        Dim c1 As Range 'First cell of filtered Range...
        Set c1 = Range("A1")
    
        Dim r1 As Long  'Last row of filtered Range...
        r1 = c1.SpecialCells(xlLastCell).Row
    
        Selection.AutoFilter    'Unfiltered...
        ActiveSheet.Range(c1, Cells(r1, 1)).AutoFilter  'Filter new Range...
    End If
End Sub

[Xx7]

A simpler option would be a pivot table.

Go to the C7 on the "Pivot" TAB and change the date. Anytime you add new data on the "Data" TAB, just right click the pivot table and select "Refresh". It will be updated automatically.

[HC1994]

Yes, you are correct. Mine didn't include the spaces. See if this works for you.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    
        Dim c1 As Range 'First cell of filtered Range...
        Set c1 = Range("A1")
    
        Dim r1 As Long  'Last row of filtered Range...
        r1 = c1.SpecialCells(xlLastCell).Row
    
        Selection.AutoFilter    'Unfiltered...
        ActiveSheet.Range(c1, Cells(r1, 1)).AutoFilter  'Filter new Range...
    End If
End Sub

Sweeet! It worked Thanks very much buddy!

[HC1994]

A simpler option would be a pivot table.

Go to the C7 on the "Pivot" TAB and change the date. Anytime you add new data on the "Data" TAB, just right click the pivot table and select "Refresh". It will be updated automatically.

Nice option! Thank you, I'll will use this when I am presenting the sheet! Thank you :D