0

Basically I am trying to read the filters set by the user in the Excel, remove the filters applied, perform some processing and set the same filters back as set by the user. Following are the lines of code:

Excel.Filters filters = null;
if (Worksheet.AutoFilter != null && Worksheet.AutoFilterMode == true)
{
    filters = Worksheet.AutoFilter.Filters;
    Worksheet.AutoFilter.ShowAllData();
}
/*
  other operations
*/
if (filters != null)
{
    //set the filters back
    foreach (Excel.Filter filter in filters)
    {
       
    } 
}

I haven't found success in setting filters back. Even I am not sure if it's the right way to set the filters back.

Any help or suggestion would be great.

Thanks.

Jayakrishnan
  • 4,232
  • 2
  • 23
  • 35
  • 1
    When you store the `Filters` collection in `filters`, you are storing the collection associated with the autofilter on the sheet - when you do `ShowAllData`, you modify that collection, in both places (think of `Worksheet.AutoFilter.Filters` and `filters` as pointing to the same list of objects - modifying one of the objects changes both lists). You need to to save the status of each filter yourself, or at least the ones that are on. – NetMage Dec 29 '20 at 20:56
  • 1
    Note that reapplying the autofilter requires using the `AutoFilter` method on a `Range`, and that `AutoFilter.Filters` does not return enough information to restore all filters. – NetMage Dec 29 '20 at 21:12
  • 1
    See [this answer](https://stackoverflow.com/a/9490281/2557128) for some idea of what is involved (in VBA). – NetMage Dec 29 '20 at 21:20
  • Thanks @NetMage, your suggestion and the answer link worked perfectly for me :) – Jayakrishnan Dec 30 '20 at 11:58

0 Answers0