[c#] How to get the range of occupied cells in excel sheet

I use C# to automate an excel file. I was able to get the workbook and the sheets it contains. If for example I have in sheet1 two cols and 5 rows. I wanted o get the range for the occupied cells as A1:B5. I tried the following code but it did not give the correct result. the columns # and row # were much bigger and the cells were empty as well.

     Excel.Range xlRange = excelWorksheet.UsedRange;
     int col = xlRange.Columns.Count;
     int row = xlRange.Rows.Count;

Is there another way I can use to get that range?

The only way I could get it to work in ALL scenarios (except Protected sheets) (based on Farham's Answer):

It supports:

• Scanning Hidden Row / Columns

• Ignores formatted cells with no data / formula

Code:

// Unhide All Cells and clear formats
sheet.Columns.ClearFormats();
sheet.Rows.ClearFormats();

// Detect Last used Row - Ignore cells that contains formulas that result in blank values
int lastRowIgnoreFormulas = sheet.Cells.Find(
                "*",
                System.Reflection.Missing.Value,
                InteropExcel.XlFindLookIn.xlValues,
                InteropExcel.XlLookAt.xlWhole,
                InteropExcel.XlSearchOrder.xlByRows,
                InteropExcel.XlSearchDirection.xlPrevious,
                false,
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value).Row;
// Detect Last Used Column  - Ignore cells that contains formulas that result in blank values
int lastColIgnoreFormulas = sheet.Cells.Find(
                "*",
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value,
                InteropExcel.XlSearchOrder.xlByColumns,
                InteropExcel.XlSearchDirection.xlPrevious,
                false,
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value).Column;

// Detect Last used Row / Column - Including cells that contains formulas that result in blank values
int lastColIncludeFormulas = sheet.UsedRange.Columns.Count;
int lastColIncludeFormulas = sheet.UsedRange.Rows.Count;

This is tailored to finding formulas but you should be able to expand it to general content by altering how you test the starting cells. You'll have to handle single cell ranges outside of this.

    public static Range GetUsedPartOfRange(this Range range)
    {
        Excel.Range beginCell = range.Cells[1, 1];
        Excel.Range endCell = range.Cells[range.Rows.Count, range.Columns.Count];

        if (!beginCell.HasFormula)
        {
            var beginCellRow = range.Find(
                "*",
                beginCell,
                XlFindLookIn.xlFormulas,
                XlLookAt.xlPart,
                XlSearchOrder.xlByRows,
                XlSearchDirection.xlNext,
                false);

            var beginCellCol = range.Find(
                "*",
                beginCell,
                XlFindLookIn.xlFormulas,
                XlLookAt.xlPart,
                XlSearchOrder.xlByColumns,
                XlSearchDirection.xlNext,
                false);

            if (null == beginCellRow || null == beginCellCol)
                return null;

            beginCell = range.Worksheet.Cells[beginCellRow.Row, beginCellCol.Column];
        }

        if (!endCell.HasFormula)
        {
            var endCellRow = range.Find(
            "*",
            endCell,
            XlFindLookIn.xlFormulas,
            XlLookAt.xlPart,
            XlSearchOrder.xlByRows,         
            XlSearchDirection.xlPrevious,
            false);

            var endCellCol = range.Find(
                "*",
                endCell,
                XlFindLookIn.xlFormulas,
                XlLookAt.xlPart,
                XlSearchOrder.xlByColumns,
                XlSearchDirection.xlPrevious,
                false);

            if (null == endCellRow || null == endCellCol)
                return null;

            endCell = range.Worksheet.Cells[endCellRow.Row, endCellCol.Column];
        }

        if (null == endCell || null == beginCell)
            return null;

        Excel.Range finalRng = range.Worksheet.Range[beginCell, endCell];

        return finalRng;
    }
}

These two lines on their own wasnt working for me:

xlWorkSheet.Columns.ClearFormats();
xlWorkSheet.Rows.ClearFormats();

You can test by hitting ctrl+end in the sheet and seeing which cell is selected.

I found that adding this line after the first two solved the problem in all instances I've encountered:

Excel.Range xlActiveRange = WorkSheet.UsedRange;

You should try the currentRegion property, if you know from where you are to find the range. This will give you the boundaries of your used range.

See the Range.SpecialCells method. For example, to get cells with constant values or formulas use:

_xlWorksheet.UsedRange.SpecialCells(
        Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants |
        Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeFormulas)

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

"range" will now be the occupied cell range

dim lastRow as long   'in VBA it's a long 
lastrow = wks.range("A65000").end(xlup).row

I had a very similar issue as you had. What actually worked is this:

iTotalColumns = xlWorkSheet.UsedRange.Columns.Count;
iTotalRows = xlWorkSheet.UsedRange.Rows.Count;

//These two lines do the magic.
xlWorkSheet.Columns.ClearFormats();
xlWorkSheet.Rows.ClearFormats();

iTotalColumns = xlWorkSheet.UsedRange.Columns.Count;
iTotalRows = xlWorkSheet.UsedRange.Rows.Count;

IMHO what happens is that when you delete data from Excel, it keeps on thinking that there is data in those cells, though they are blank. When I cleared the formats, it removes the blank cells and hence returns actual counts.

You should not delete the data in box by pressing "delete", i think thats the problem , because excel will still detected the box as "" <- still have value, u should delete by right click the box and click delete.

Bit old question now, but if somebody is looking for solution this works for me.

using Excel = Microsoft.Office.Interop.Excel;

Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel.Application app = excel.Application;
Excel.Range all = app.get_Range("A1:H10", Type.Missing);