0

This is a follow-up question to an old question:
How to get the range of occupied cells in excel sheet

Quoting from that question:

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 share|improve this answer answered Aug 18 '09 at 9:03 Zurb

it returns always time blank cells, which i think question author wants to "not to get" that blank cells.– JavidanApr 23 at 7:30

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)

share|improve this answer answered Aug 16 '09 at 14:23 Joe Erickson

Back to my question...
I tried to use this method which generates this exception:
System.Runtime.InteropServices.COMException: 'SpecialCells method of Range class failed'

Here is my code:

using Excel = Microsoft.Office.Interop.Excel;  
using System.Reflection;

Excel.Application xlapp = new Excel.Application();  
Excel.Workbook wbook = xlapp.Workbooks.Open(tbxFilename.Text);  
Excel.Worksheet wsheet = wbook.Sheets[1];

Excel.Range range = wsheet.UsedRange;  
tbxOutput.AppendText($"UsedRange: Rows = {range.Rows.Count}, Cols `enter code here`= {range.Columns.Count}\r\n");  

range = wsheet.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeConstants |    // generates Exception  
   Excel.XlCellType.xlCellTypeFormulas, Missing.Value);  
tbxOutput.AppendText($"Rows = {range.Rows.Count}, Cols = {range.Columns.Count}\r\n");

What is wrong with my code? Thanks!

Mark Green
  • 19
  • The question you linked to has several answers, including one that uses the same code that is throwing your error. Did you read the comment after that answer? It appears the bar “|” character (I am assuming it was used as an “or”) in the command has been deprecated and no longer works in newer versions of Excel. Assuming the bar “|” character no longer works, what exactly are you trying to select from the worksheet and what is not working as expected? – JohnG May 06 '21 at 21:48
  • Sorry, I was looking at a copy of this thread that I saved years ago. My mistake for not going back to the live thread first. I am trying to find the range of occupied cells in the worksheet, i.e. cells containing formulas or constants. – Mark Green May 06 '21 at 22:18
  • What problem are you having? `UsedRange` will grab both cells containing formulas and constants. Granted, you will need to loop through the cells to see which cells are formulas and which cells are constants. I suggest you edit your question and be specific about what you want and what is not working. Almost all the references to the other question are of no help so remove the unnecessary fluff. State what you want to do, what you have tried and what is not working as expected. – JohnG May 06 '21 at 23:40
  • There are many questions about how to get the range of occupied cells in an Excel worksheet. I thought it was a well-known problem that UsedRange includes any cell that was ever occupied or formatted even though it is empty now. UsedRange will often return 100s or 1000s of rows and columns for a relatively small worksheet. I've seen many questions complaining about this. That is why I am searching for a method that works better. I can't include much information in a Reply because it limits me to a few hundred chars. – Mark Green May 07 '21 at 00:27
  • I am aware that `UsedRange` will do as you describe and return possibly many “empty” cells. I have an answer for those cases… [Fastest method to remove Empty rows and Columns From Excel Files using Interop](https://stackoverflow.com/questions/40574084/fastest-method-to-remove-empty-rows-and-columns-from-excel-files-using-interop/40726309#40726309) … Another issue with Interop is it is notoriously slow if the worksheets are large. Are you stuck with using interop? There are other third party libraries that do not have this issue and are better IMHO. EPPlus is a popular option. – JohnG May 07 '21 at 00:35
  • I don't have empty rows at the top or empty columns on the left so the solution you suggested doesn't apply in my case. Where UsedRange has a problem is with empty rows at the bottom or empty columns to the right. I think it is necessary to search every cell in UsedRange to determine if it has a value (constant or formula). – Mark Green May 07 '21 at 15:22
  • Scroll down a little further into my answer. There is an added portion from Hadi that removes the extra rows and columns after the data. Just as an aside… There is a way to permanently delete those rows from the Excel worksheet so `UsedRange` will not return them. – JohnG May 07 '21 at 15:32
  • Example, let’s say the Excel file has 100 rows of data, but `UsedRange` returns 1000 rows. Open the Excel file, scroll down to the row just past the data (101), then on the left side of the worksheet, in the column with the numbers, hold the Shift key and select ALL the rows from 101 to 1001 or more I would go to row 1100. Then right-click and “DELETE” those rows and shift up. Clearing will not work. After this the `UsedRange` should return the proper cells without the extra empty cells. The same idea applies to the columns. – JohnG May 07 '21 at 15:33

0 Answers0