Turn Off Filters

I used the following code because xlSheet.AutoFilterMode = false throws as COMException for me even though xlSheet.AutoFilterMode is true.

if (xlSheet.AutoFilter != null && xlSheet.AutoFilterMode == true)
{
    xlSheet.AutoFilter.ShowAllData();
}

As mentioned by Sid Holland, this clears all filters while also retaining the filter arrows.


I would test first to see if a filter has been applied and then deactivate it if it has:

if (xlSheet.AutoFilter != null)
{
    xlSheet.AutoFilterMode = false;
}

That should remove any filtering that has been applied and remove the filter arrow buttons.


You can disable all filters by calling the AutoFilter method on the range twice with no parameters.

sheet.Cells.AutoFilter();
sheet.Cells.AutoFilter();

I'm not very Interop savvy, but you may need to pass 5 Type.Missing or Missing.Value as parameters.

The first call will turn AutoFilter off if it's on, and the second will turn it on if it's off and vice versa. But in either case there will no longer be hidden cells due to filtering.

Tags:

C#

Excel