Resolved Excel Interop Autofilter Range

[tim8w]

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'

 

[tim8w]

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!