Reordering Excel Table Columns with Filters Intact

Question

I am running into an issue with Excel 2007 via VSTO 3.0. In my project, I need to shuffle columns around occasionally. Normally, this works fine but I have recently found that an issue arises when the data is filtered and the fitlered data is non-contiguous.

For example, using this data set:

Ohio        Eastern
Kentucky    Eastern
Illinois    Central
California  Pacific
Florida     Eastern

If the filter on column two excludes either Pacific or Central, the following code produces an error.

ListObject table = FindReportTable();
// Get data to shuffle around.
ListColumn tempColumn = table.ListColumns.Add(missing);
Range range1 = table.ListColumns[column1].Range;
Range range2 = table.ListColumns[column2].Range;
Range tempRange = tempColumn.Range;
// Swap the rows.
range1.Copy(tempRange); // This is where an error is thrown.
range2.Copy(range1);
tempRange.Copy(range2);
tempColumn.Delete();

The error thrown is a System.Runtime.InteropServices.COMException with an error code of -2146827284 and message "That command cannot be used on multiple selections".

I have tried many things and none seem to work completely. If I use table.AutoFilter.ShowAllData(), I can continue with no problems, but I have then cleared the user's filters and cannot get them back with table.AutoFilter.ApplyFilter() as I assumed.

I cannot reliably store and recreate the filters for two reasons: one, iterating through table.AutoFilter.Filters and storing the filter's data can cause problems if the dataset in the filter is too large. Referring to my example data, if the filter was to exclude Central from column 2, attempting to retrieve the filter's information causes an interop error. Second, to recreate the filter, I need to know what row the filter was originally on to pass into the table.Range.AutoFilter(object, object, XlFilterOperator, object, object) method.

I have been banging my head against this for too long now, and can't seem to find a solution in my own head or on the vast internets. Any help would be much appreciated.

Answer 1

I would look into removing and recreating the filters. If you're seeing problems, then you should fix those problems.

Look at the ListObject.AutoFilter.Filters collection.

Look here for more information.