How to get the range of occupied cells in excel sheet

Question

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?

What kind of data you have on the target range. I used UsedRange property before with no issues) as long as the workbook and sheet are the active ones. — mas_oz2k1, Mar 08 '10 at 02:43

Farhan · Answer 1 · 2017-09-18T14:50:48.390

62

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.

edited Sep 18 '17 at 14:50

answered Jan 24 '13 at 18:55

Farhan

2,535
4
32
54

3

This is the only answer I have seen without issues. This resolve issues like: Detecting empty formatted cells. Detecting values inside hidden Row / Columns. – Gerhard Powell Mar 03 '14 at 16:30
2

xlWorkSheet.Columns.ClearFormats(); xlWorkSheet.Rows.ClearFormats(); Great answer – Vibeeshan Mahadeva Mar 26 '15 at 09:59
3

It is a very useful answer, but be aware that this requires write-access to the spreadsheet. – H H Apr 02 '15 at 09:43
4

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..? – Mike Gledhill Oct 30 '15 at 11:00
2

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: http://stackoverflow.com/questions/39931543/c-sharp-excel-date-value-is-coming-as-numeric-value – AllSolutions Oct 08 '16 at 14:11
This works in JScript.net as well. It should work in Javascript as well haven't tested! – V.J. Apr 19 '17 at 07:09
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. – AltF4_ Sep 18 '17 at 14:43

score 20 · Answer 2 · answered Aug 18 '09 at 09:03

20

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

answered Aug 18 '09 at 09:03

Zurb

738
1
5
18

1

it returns always time blank cells, which i think question author wants to "not to get" that blank cells. – Javidan Apr 23 '13 at 07:30

score 4 · Answer 3 · answered Aug 16 '09 at 14:23

4

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)

answered Aug 16 '09 at 14:23

Joe Erickson

7,077
1
31
31

3

(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...) – Mike Gledhill Oct 30 '15 at 12:10

Gerhard Powell · Answer 4 · 2014-03-24T16:27:43.873

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;

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, Oct 30 '15 at 12:17
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". — Mike Gledhill, Nov 03 '15 at 15:20
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? — AllSolutions, Oct 08 '16 at 14:18
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 — AltF4_, Sep 18 '17 at 14:41

score 0 · Answer 5 · answered Aug 16 '09 at 16:09

0

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

answered Aug 16 '09 at 16:09

iDevlop

24,841
11
90
149

1

This solution only works in VBA. It's not a C# solution – Mar 10 '14 at 16:00

score 0 · Answer 6 · answered Aug 15 '14 at 08:32

0

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;

answered Aug 15 '14 at 08:32

Simon

1

Is there a way to programmatically find out the cell Excel will show when using Ctrl + End? – AllSolutions Oct 08 '16 at 14:20

score 0 · Answer 7 · answered Jan 26 '11 at 18:30

0

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

answered Jan 26 '11 at 18:30

sidon

1,434
1
17
30

7

This assumes that you know exactly what range you need. The other solutions provided are more general and work perfectly. – Dan Mar 30 '12 at 08:24
@Dan: Actually none of them works for me - always get 15242 rows when it should be just over 2000 – 537mfb May 09 '12 at 18:05
1

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. – Dan May 10 '12 at 06:11

score 0 · Answer 8 · answered Apr 12 '18 at 11:16

0

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.

answered Apr 12 '18 at 11:16

Utsav Jha

1

MikeJ · Answer 9 · 2018-09-21T16:31:24.347

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

score 0 · Answer 10 · answered Apr 30 '19 at 03:15

0

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.

answered Apr 30 '19 at 03:15

codeislife

15
8

How to get the range of occupied cells in excel sheet

10 Answers10

Linked

Related