This project has moved. For the latest updates, please go here.

How to find (empty) rows in Excel?

Apr 10, 2013 at 7:49 AM
Edited Apr 10, 2013 at 8:37 AM
I was looking at the api, but i was wondering how to find empty rows (rows with empty cells) to eliminate these kind of rows

Well, i can use maybe
NetOffice.ExcelApi.Range Find

but i can't find any worth examples. Maybe this is the wrong way to do it?!
Apr 10, 2013 at 8:38 AM
Edited Apr 10, 2013 at 8:39 AM
The second thing is that the fields and methods are not described well IMHO, so the learning curve is really steep.
It would be better to set some references to the msdn library where the documentation problem could be disarmed a little bit ;)
Apr 10, 2013 at 2:36 PM
whitewoolve,

Google "VBA delete empty rows" you'll find a ton of information on the topic. E.g., Range("A1:A100").SpecialCells(xl...).Delete (I don't remember what the actual constant was).
Apr 15, 2013 at 1:13 PM
Thx John,

but i solved it with XlDeleteShiftDirection constant
                    range.EntireRow.Delete(Excel.Enums.XlDeleteShiftDirection.xlShiftUp);
The only interesting column is the first column in every row, so i iterated through the rows and deleted the concerning rows
Apr 15, 2013 at 2:53 PM
Yes, that is another way to do it. Just takes a really long time if you have a nontrivial amount of information.
Apr 16, 2013 at 11:19 AM
Everything is relative :)) It counts on the number of rows :) But i havn't done a benchmark for 30 rows
Apr 16, 2013 at 3:25 PM
Maybe something with UsedRange might help you out.
                 workbook = xlsApp.Workbooks.Open(<file>, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                Type.Missing, Type.Missing);
                Excel.Worksheet workSheet;
                foreach (Excel.Worksheet sheet in workbook.Sheets)
                {
                    workbook.Activate();
                    sheet.Activate();
                    foreach (Excel.Shape shape in sheet.Shapes)
                    {
                              sheet.UsedRange.Copy(Missing.Value);  //copies every in the range that is currently being used.
                    }
                 }