Excel VBA Autofilter on Open

[Oxalate]

Dear Forum members,

I would like to add VBA code to my file which on open activates, and sets the Excel Autofilter (just the normal basic filter with arrows)on the same entire row.

The story behind is that we have a shared Excel file in our group, and sometimes other users deactivate the filter and save...or move to other rows and then save etc.
It would be nice to have the workbook on open, always filter enabled on the entire row.

So basically:
onOpen
Set Autofilter row 10.

Thank you.

 

[Roderick_E]

Press alt+F11 and paste this code, changing Sheet1 to the sheet you want to filter

Private Sub Workbook_Open()

            If Sheet1.AutoFilterMode = False Then
            Sheet1.Rows(10).Select
    Sheet1.Cells.AutoFilter
    End If
End Sub

 

[Oxalate]

Thank you for your help.
This code works perfectly if there is no filter set on open, which is great.
Is it possible to adjust the code also that if the file was saved with some other row filtered, e.g. row 5, that it always filters row 10 on open by default?

 

[Roderick_E]

Thank you for your help.
This code works perfectly if there is no filter set on open, which is great.
Is it possible to adjust the code also that if the file was saved with some other row filtered, e.g. row 5, that it always filters row 10 on open by default?

Glad it worked. Here's modified:

Private Sub Workbook_Open()
On Error GoTo exitit:
 If Sheet1.AutoFilterMode = False Then
 Sheet1.Rows(10).AutoFilter
    Else
    'reset to row 10
    Sheet1.Cells.AutoFilter
    Sheet1.Rows(10).AutoFilter
    End If
    Exit Sub
exitit:
End Sub

 

[Oxalate]

That's it, exactly what I needed.
It works perfectly.
Thank you very much.