Turn Off Filters

Question

So, I'm doing some searching in an Excel document, but it is very common for others to turn on filters and leave them on. When those filters are on, those cells are not included in the worksheet's Cells range.

Is there a way to turn off these custom filters so that I can still get to all of the cells in the sheet?

This is the way I use to find the method

Microsoft.Office.Interop.Excel.Range find = sheet.Cells.Find(tapeID, Type.Missing,
Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlPart, 
Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing); 

When the filters are on, I get a null object returned and can't do anything but with the filters off, I get what I need.

Any hints on turning the filters off?

Answer 1

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.

Answer 2

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.

Answer 3

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.