Find last cell with text in it

Question

I want to be able to find the last cell that contains text. Currently I'm using Interop this works fine to find the usedRnage however I want it to only find the last cell used that contains text. In this example the the last cell used is J32 I want it to only find the last value which contains text so it should be A26.

My code is as fallows

            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;


            // Detect Last used Row / Column - Including cells that contains formulas that result in blank values
           var iTotalColumns = xlWorkSheet.UsedRange.Columns.Count;
           var iTotalRows = xlWorkSheet.UsedRange.Rows.Count;

            //Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
            //Excel.Range lastRange = xlWorkSheet.get_Range("A1", last);

            Excel.Application xlCell;

Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
            Excel.Range usedRange = xlWorkSheet.get_Range("A1", last);


            int lastUsedRow = last.Row;
            int lastUsedColumn = last.Column;

            xlWorkBook.Close(@"C:\Users\Craig\Desktop\testCell.xlsx", true, null);
            xlApp.Quit();

Answer 1

Based on the fact that the used range should contain the last cell containing text, you can loop from the last cell in the used range to the first cell (one loop for columns and one for rows) to find the last cell on the worksheet containing text. Check out the following code:

Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
Microsoft.Office.Interop.Excel.Range range;

string str;


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


range = xlWorkSheet.UsedRange;

Microsoft.Office.Interop.Excel.Range last = xlWorkSheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);


int lastUsedRow = last.Row;
int lastUsedColumn = last.Column;

int lastColumn = -1;

// Loop to find the last column containing text
for(int i = lastUsedColumn; i > 0; i--)
{
    if(xlWorkSheet.Application.WorksheetFunction.CountA(xlWorkSheet.Columns[i]) > 0)
    {
        lastColumn = i;
        break;
    }
}

int lastRow = -1;

// Loop to find the last row containing text
for (int i = lastUsedRow; i > 0; i--)
{
    if (xlWorkSheet.Application.WorksheetFunction.CountA(xlWorkSheet.Rows[i]) > 0)
    {
        lastRow = i;
        break;
    }
}

Console.WriteLine("Last row containing text: " + lastRow);
Console.WriteLine("Last column containing text: " + lastColumn);

// Clear formatting of all columns from last cell with text to last cell in used range
// For columns, you will 
xlWorkSheet.Range[xlWorkSheet.Cells[lastRow, lastColumn+1], xlWorkSheet.Cells[lastUsedRow, lastUsedColumn]].ClearFormats();

// Clear formatting of all rows from last cell with text to last cell in used range
xlWorkSheet.Rows[(lastRow+1) + ":" + lastUsedRow].ClearFormats();

Console.ReadLine();

xlWorkBook.Close(true);
xlApp.Quit();