0

I am trying to remove a certain number of columns from an Excel Spreadsheet according to the cell value in the first or second rows of the used range. So if a cell in that range contains a certain string, I want to delete the entire column which has around 3500 lines/entries.

    List<int> ColumnsToBeGone = new List<int>();
    tab = (Worksheet)AllSheets.get_Item(SheetName);
    range = (Range)tab.UsedRange;
    object[,] allValues = (object[,])range.Cells.Value;

        for (int i = 0; i < 2; ++i)
        {
            for(int j = 0; j<range.Columns.Count; ++j)
            {
                CellRow = i + 1; CellColumn = j + 1;
                if (allValues[CellRow, CellColumn] != null)
                {
                    CellVal = allValues[CellRow, CellColumn].ToString();

                    if (CellVal != null)
                    {
                        if (CellVal.ToLower() == "item1" ||
                           CellVal.ToLower() == "item2" ||
                           CellVal.ToLower() == "item3" ||
                           CellVal.ToLower() == "item4" ||
                           CellVal.ToLower() == "item5" ||
                           CellVal.ToLower().Contains("item") ||
                           CellVal.ToLower().Contains("idem"))
                        {
                            ColumnsToBeGone.Add(CellColumn);
                        }
                    }
                }
            }
        }

Up till this point, I have no performance issues but it ends up taking 30 - 60 seconds per column delete using the following. 

        foreach(int column in ColumnsToBeGone)
        {
            tab.Columns[column].Delete();
        }

At this point is it a RAM/Processor issue (4GB 2nd gen i3 @ 3.30GHz) or are there more efficient ways to delete a column with this many entries?

whijazi
  • 3
  • 4
  • Some handy tips in the answers to http://stackoverflow.com/questions/12391786/effect-of-screen-updating – stuartd Mar 08 '17 at 14:04
  • Excel (and interop especially) is notoriously slow. Turn off screen updating and calculation before you do anything, that should help. – DavidG Mar 08 '17 at 14:05
  • Have a look at this (VBA) thread, it should help you: http://codereview.stackexchange.com/questions/157044/looping-through-cells-and-deleting-column – jkpieterse Mar 08 '17 at 14:57

0 Answers0