View original (French)

VBA Excel 2010: Remove all filters on closing

Solved
Villette54 Posted messages 300 Registration date   Status Member Last intervention   -  
 ExpertExcel -
Hello everyone,

I would like to create a macro that removes all filters that may have been applied to the file either upon closing or upon opening it, in order to ensure that I open the file with no active filters.

I don't know if it's important, but my filters are on row 3 and go from column B to column AH.

Thank you in advance :)

4 answers

  1.
    f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hello,

    a way to do this when opening the file

    code in thisworkbook to save the file as xlsm 

    Private Sub Workbook_Open() For i = 1 To Sheets.Count Sheets(i).Select Cells.Select Selection.AutoFilter Next End Sub


    or when closing 

    Private Sub Workbook_BeforeClose(Cancel As Boolean) For i = 1 To Sheets.Count Sheets(i).Select Cells.Select Selection.AutoFilter Next End Sub


    Have a nice day
    0
  2. Villette54 Posted messages 300 Registration date   Status Member Last intervention   28
     
    You're welcome :)
    0
  3. Villette54 Posted messages 300 Registration date   Status Member Last intervention   28
     
    Hello everyone,

    I'm getting back to you because I've just realized (better late than never) that I didn't express myself correctly.

    I do not wish for the removal of the filter tool, just the reset of the filters to default on my first two pages (the others being graphs).

    I hope I have expressed myself clearly this time,

    Thank you in advance and have a good day.
    0
  4. ExpertExcel
     
    Here is a simple solution

    Dim i as integer
    Const NBR_FEUILLES = 2

    on error resume next
    ' necessarily the first 2 sheets of your workbook
    For i = 1 to NBR_FEUILLES
    worksheets(i).cells.AutoFilter ' clears the filters
    worksheets(i).cells.AutoFilter ' resets it to default
    next

    Fred
    http://expertise-excel.com/
    0