XlCall.xlcFilter

[Chad Kelley]

Hey everyone,

I'm Trying to speed up an Autofilter Function i currently have in a large spreadsheet.  I currently have the Following:

Sheet.Range["A:A"].AutoFilter(Field: 1, Criteria1: ">=" + StartValue, Operator: XlAutoFilterOperator.xlFilterValues, Criteria2: "<=" + EndValue);

Where StartValue and EndValue are numbers from the "A" Column.

Here is what i am Trying:

  [ExcelCommand(Name = "MyTestCommand", ShortCut = "^Q")]
        public void ToggleSheetFilterAdvanced(Worksheet Sheet, double StartValue, double EndValue)
        {

            // Get the Excel application object
            Application excelApp = (Application)ExcelDnaUtil.Application;

            // Get the range to apply the filter to
            Range range = excelApp.Selection;

            // Define the filter criteria
            object[] filterCriteria = new object[] { "1" };

            // Call the XlCall.xlcFilter function to apply the filter
            XlCall.Excel(XlCall.xlcFilter, range, filterCriteria);

            }
        }

Initially i'm thinking the C API calls might be much faster but I cannot find any reason why this is throwing an exception.

anyone have any ideas?

[Govert van Drimmelen]

The C API calls would need to take an ExcelReference object, instead of the COM Range object.

You can try to start with a C API call to get the selection (as an ExcelReference object)

   var selection = XlCall.Excel(XlCall.xlfSelection);

then it might work better.

I'm not sure whether the C API is faster or as full featured as the COM approach for this kind of sheet manipulation, but it's worth checking.

-Govert