How to get the range of occupied cells in excel sheet

by

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.

More Related Contents:

  1. Fastest way to interface between live (unsaved) Excel data and C# objects
  2. Accessing Excel Custom Document Properties programmatically
  3. How to detect installed version of MS-Office?
  4. Export DataTable to Excel with EPPlus
  5. Excel Interop – Efficiency and performance
  6. Format an Excel column (or cell) as Text in C#?
  7. How to write some data to excel file(.xlsx)
  8. Excel to DataTable using EPPlus – excel locked for editing
  9. C# create/modify/read .xlsx files
  10. How to use WebBrowser control DocumentCompleted event in C#?
  11. Microsoft Office Excel cannot access the file ‘c:\inetpub\wwwroot\Timesheet\App_Data\Template.xlsx’
  12. How to reference Microsoft.Office.Interop.Excel dll?
  13. How to read single Excel cell value
  14. Get instance of Excel application with C# by Handle
  15. BUG: Can’t choose dates on a DatePicker that fall outside a floating VSTO Add-In
  16. Programmatically getting the last filled excel row using C#
  17. EPPlus Large Dataset Issue with Out of Memory Exception
  18. Reading Excel Files as a Server Process
  19. Is csv with multi tabs/sheet possible?
  20. No definition found for GetActiveObject from System.Runtime.InteropServices.Marshal C#
  21. Reading from Excel (Range into multidimensional Array) C#
  22. How to know if a cell has an error in the formula in C#
  23. how to solve Exception:Call was rejected by callee. (Exception from HRESULT: 0x80010001 (RPC_E_CALL_REJECTED)) in C#?
  24. C# – How to add an Excel Worksheet programmatically – Office XP / 2003
  25. How to read more than 256 columns from an excel file (2007 format) using OLEDB
  26. Using ExcelDataReader to read Excel data starting from a particular cell
  27. openxml spreadsheat save-as
  28. EPPlus – LoadFromCollection – Text converted to number
  29. Import Excel to Datagridview
  30. .Net Excel Interop Deleting a worksheet

Leave a Comment