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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

dim lastRow as long    in VBA it s a long  lastrow   wks range  A65000   end xlup  row

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to c#

Examples related to excel

Examples related to automation