0

Let's say I have an excel document and I'm trying to get the range where the rows have values in their cells. (I'm using C# and interop).

excel.Application appExcel = new excel.Application();
excel.Workbook WbExcel;
excel.Worksheet sheet;

WbExcel = appExcel.Workbooks.Open(path);
sheet = WbExcel.Sheets["Sheet1"];
sheet.Activate();

excel.Range selectedRange = sheet.Range["A4", sheet.UsedRange.SpecialCells(excel.XlCellType.xlCellTypeLastCell)];

appExcel.Visible = true;

The problem comes when some rows are formatted but without values in their cells or some cell had a value before, they are selected too!.

I tried selecting only visible values: 

 selectedRange.SpecialCells(excel.XlCellType.xlCellTypeVisible).Select();

but it did not work.

Does anyone have a sugestion how could I make a range, avoiding rows with empty values in their cells or empty formatted cells?

Jorge G.
  • 83
  • 9
  • `xlCellTypeConstants` for values and `xlCellTypeFormulas` for formulas – Slai Jan 30 '17 at 22:12
  • I created a solution to remove all empty rows and columns from a `usedRange` using a 2 dimensional object array to speed up the process. I am guessing that this may be overkill; however it does properly remove completely empty rows and columns even those that previously had text. Hope this may help. [Remove Empty rows and Columns From Excel Files Faster using Interop](http://stackoverflow.com/questions/40574084/remove-empty-rows-and-columns-from-excel-files-faster-using-interop/40726309#40726309) – JohnG Jan 31 '17 at 01:18

0 Answers0