By Sarah


2009-08-16 14:10:26 8 Comments

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?

10 comments

@codeislife 2019-04-30 03:15:34

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.

@MikeJ 2018-09-13 14:56:47

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;
    }
}

@Utsav Jha 2018-04-12 11:16:05

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.

@Farhan 2013-01-24 18:55:30

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.

@Gerhard Powell 2014-03-03 16:30:31

This is the only answer I have seen without issues. This resolve issues like: Detecting empty formatted cells. Detecting values inside hidden Row / Columns.

@VibeeshanRC 2015-03-26 09:59:31

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

@Henk Holterman 2015-04-02 09:43:06

It is a very useful answer, but be aware that this requires write-access to the spreadsheet.

@Mike Gledhill 2015-10-30 11:00:03

Ah heck. Yes, this does work (previously, Excel was reporting I had 16,349 columns, rather than just 13 !) but - to state the obvious - it removes all the cell formatting. Is there anyway to get the "real" number of columns without doing this..?

@AllSolutions 2016-10-08 14:11:22

Good solution. But the drawback with this solution, which I found out the hard way is that if you need to read the values as they are displayed in Excel, then by clearing all formatting you will not be able to do it. I found this while solving this: stackoverflow.com/questions/39931543/…

@V.J. 2017-04-19 07:09:57

This works in JScript.net as well. It should work in Javascript as well haven't tested!

@AltF4_ 2017-09-18 14:43:44

Be very aware of this clear formatting and changing columns like Date as pointed out by @AllSolutions , I found "Gerhard Powel"'s answer on this page corrects this issue.

@Simon 2014-08-15 08:32:18

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;

@AllSolutions 2016-10-08 14:20:01

Is there a way to programmatically find out the cell Excel will show when using Ctrl + End?

@Gerhard Powell 2014-03-03 16:42:57

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;

@Mike Gledhill 2015-10-30 12:17:22

WOW. Brilliant code. You're right, this is the only code I've found which correctly obtains the number of columns/rows in a Worksheet. VSTO's own "SpecialCells(XlCellType.xlCellTypeLastCell)" was sometimes telling me my 9-column worksheet contained 16349 columns of data. Using your code, I get the 9 I was looking for. Fantastic.

@Mike Gledhill 2015-11-03 15:20:32

Just to add, after a lot of testing, we found that this code doesn't seem to detect Excel cells containing values, on columns/rows that've been "grouped" and collapsed. Our users also complained that it was missing columns/rows containing just formulae, so we needed to do two Finds - one for "XlFindLookIn.xlValues" and one for "XlFindLookIn.xlFormulas".

@AllSolutions 2016-10-08 14:18:32

The problem with using ClearFormats is that you wont be able to read cell text exactly the way it is displayed on screen, in case that is a requirement. I had a requirement like this in one of my projects; so is there any way to find Used Range without using ClearFormats?

@AltF4_ 2017-09-18 14:41:50

Excellent answer, can i just point out that you have written 'System.Reflection.Missing.Value' three times in the column code. I have corrected it in my code

@sidon 2011-01-26 18:30:47

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);

@Dan 2012-03-30 08:24:19

This assumes that you know exactly what range you need. The other solutions provided are more general and work perfectly.

@537mfb 2012-05-09 18:05:03

@Dan: Actually none of them works for me - always get 15242 rows when it should be just over 2000

@Dan 2012-05-10 06:11:55

Well this method is definitely going to give you only 10 rows. What does your data look like? Perhaps create a new question and link to this one explaining which of these methods you have tried and the results.

@Zurb 2009-08-18 09:03:24

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

@Javidan 2013-04-23 07:30:13

it returns always time blank cells, which i think question author wants to "not to get" that blank cells.

@Patrick Honorez 2009-08-16 16:09:07

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

@user2140173 2014-03-10 16:00:49

This solution only works in VBA. It's not a C# solution

@Joe Erickson 2009-08-16 14:23:55

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)

@Mike Gledhill 2015-10-30 12:10:07

(Six years, and two painful iterations of Excel later...) This seems like exactly what I need, but with Excel 2013, it throws a "SpecialCells method of Range class failed" exception... Using "Cells.SpecialCells(XlCellType.xlCellTypeLastCell)" worked okay... but would sometimes return a hopelessly wrong answer (eg the 16,349th column in a worksheet containing 9 columns of data...)

Related Questions

Sponsored Content

42 Answered Questions

[SOLVED] How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 1019718 View
  • 1766 Score
  • 42 Answer
  • Tags:   c# .net excel file-io

38 Answered Questions

2 Answered Questions

[SOLVED] Hide Excel sheet based on value of a cell in a range

  • 2019-01-02 14:44:21
  • user3722957
  • 27 View
  • 0 Score
  • 2 Answer
  • Tags:   excel vba

6 Answered Questions

[SOLVED] How to read single Excel cell value

  • 2013-09-24 23:21:54
  • DoodleKana
  • 191796 View
  • 45 Score
  • 6 Answer
  • Tags:   c# excel

3 Answered Questions

[SOLVED] Get content of a cell given the row and column numbers

1 Answered Questions

0 Answered Questions

Delete Rows in Excel Worksheet if a specified cell is not equal to a cell in other sheet

  • 2015-08-12 13:37:07
  • Teodorico Levoff
  • 166 View
  • 0 Score
  • 0 Answer
  • Tags:   excel vbscript

2 Answered Questions

1 Answered Questions

[SOLVED] How to get the cell name(not value in it) in excel using c#

1 Answered Questions

[SOLVED] How to populate a cell in one sheet from a cell in the other using excel

  • 2012-03-13 19:46:50
  • user999684
  • 8074 View
  • 0 Score
  • 1 Answer
  • Tags:   excel

Sponsored Content