C#.Net Excel Interop Autofilter Range

Tim Alvord

2y1.1kC#

I need the following filter to work in C#.

Here is the working Macro in Excel VBA:

ActiveSheet.Range("$A$1:$XFC$13214").AutoFilter Field:=6, Criteria1:= ">=1/1/2024", Operator:=xlAnd, Criteria2:="<=1/31/2024"

Here is what I thought was the C# equivalent:

excelRange.AutoFilter(Field: 6, Criteria1: ">=1/1/2024", Operator: Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Criteria2: "<=1/25/2024");

But I get the following runtime error in C#:

System.Runtime.InteropServices.COMException: '_AutoFilter method of Range class failed'

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tim Alvord2y ago
Turns out I left out an important piece of information. The Worksheet that the Range is working on was created by doing a Worksheet.Copy(). I did a little research and found the following quote which explains the problem I think:

*Note

Whether you copy the worksheet programmatically, or the end user copies the worksheet manually, there is no code behind the new worksheet and controls on the new worksheet do not function. This is because the newly copied worksheet is a Worksheet object and not a Worksheet host item. Windows Forms controls and host controls can only be added to host items. For more information, see Programmatic limitations of host items and host controls.

So I did the Filter on the Original first, then copied it and then closed the original without saving it and everything worked!
Tim Alvord2y ago
Naimish,

Thanks for the assist. I was really hoping that it would be the date formay, but unfortunately I get the same error using the DateTime format for the date. Like I mentioned above, the Macro code:
```
ActiveSheet.Range("$A$1:$XFC$13214").AutoFilter Field:=6, Criteria1:= ">=1/1/2024", Operator:=xlAnd, Criteria2:="<=1/31/2024"
```
works fine, so I'm pretty sure that the Fields and Operator are correct....
Naimish Makwana2y ago
The error you’re encountering, System.Runtime.InteropServices.COMException: '_AutoFilter method of Range class failed', typically occurs when the AutoFilter method is unable to apply the filter as specified. This could be due to a variety of reasons, such as incorrect field numbers, incorrect criteria, or issues with the range itself.

In your case, you’re trying to filter a date range. Excel might be interpreting the date string differently based on the system’s locale settings, which could lead to the error.

One possible solution is to use DateTime objects for the criteria instead of strings. Here’s how you can modify your C# code:
```
DateTime startDate = new DateTime(2024, 1, 1);
DateTime endDate = new DateTime(2024, 1, 25);
excelRange.AutoFilter(Field: 6, Criteria1: startDate, Operator: Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Criteria2: endDate);
```
This ensures that the dates are correctly recognized regardless of the system’s locale settings. If the issue persists, I recommend checking the field number and the range to ensure they’re correct.

Thanks