Find and remove empty cells in a usedRange from an excel file C#

Question

I want to find a remove any cell which is empty (doesn't contain a value or style). I'm trying to do this using Excel.Interop. I'm not entirely sure how to go about this but I tried this.

        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        Excel.Range range;

        string str;


        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Open(@"C:\Users\Craig\Desktop\testCell.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        range = xlWorkSheet.UsedRange;

        if ((((Excel.Range)xlWorkSheet.Cells[range]).Value2.ToString() != String.Empty))
        {
            Console.WriteLine("No empty cells found");
        }
        else
            Console.WriteLine("Found empty cells");

However I get a run time saying The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG)) On my if statement. Any ideas on my issue or if I'm going about this the correct way? I've went into my Temporary files and removed them plus I removed the content of my bin folder

Are you sure you want to delete all empty single cells and not just delete empty rows and columns? I would think deleting single empty cells would mess up the structure of the spreadsheet. — JohnG, Dec 13 '16 at 19:34
I don't think the line: `((((Excel.Range)xlWorkSheet.Cells[range]).Value2.ToString() != String.Empty))` will work. You have a range of cells and you are trying to see if the WHOLE range is equal to an empty string? You will have to loop through the range to find the empty cells. — JohnG, Dec 13 '16 at 19:49

score 0 · Answer 1 · edited May 23 '17 at 12:01

I am guessing your error is possibly coming from the line:

if ((((Excel.Range)xlWorkSheet.Cells[range]).Value2.ToString() != String.Empty))

Above you are checking to see if a WHOLE range is equal to an empty string?

If you mean to simply remove empty rows and columns, I have answered this before, check the link below:

Remove Empty rows and Columns From Excel Files Faster using Interop

The above link is to speed up the looping in the used range to remove empty rows and columns. However, there is a section lower in my answer that loops through the rows and columns of the used range to delete rows that are empty and columns that are empty. If you wanted to delete single empty cells you can modify the code to loop through each column and delete the empty cells if needed. Hope this helps.

Find and remove empty cells in a usedRange from an excel file C#

1 Answers1