Finding the number of cells in a row that has data

Question

So I'm having a problem finding the number of rows in an excel document that has data in it. Here's what I have so far:

        for (int i = 2; i <= b; i++)
        {
            if (!(worksheet.get_Range("A" + i, misValue).Formula == null))
            {
                a.Add(worksheet.get_Range("A" + i, misValue).Formula);
            }
        }

At the moment I'm just crudely shuffling through a large number of lines, questioning whether it's null or not, then adding the contents to a list. There has to be an easier way that google has yet to show me. Thanks for the help in advanced

Just previously in code (this isn't the whole program) (int b = worksheet.Rows.Count;) — DJ Olker, Sep 03 '13 at 17:26
what is misValue, are you checking all rows of many columns or all rows of one column? — Ross Larson, Sep 03 '13 at 17:40
Somewhere in the code (either yours or on a lower level) the worksheet has to be 'parsed' out. — Nate-Wilkins, Sep 03 '13 at 17:42
One column, misValue is just "System.Reflection.Missing.Value". The way it's setup works, but is painfully slow. It takes an average of a tenth of a second to process one cell (with test data) but once applied on a larger scale, speed could become an issue. — DJ Olker, Sep 03 '13 at 17:45
Two links that might help: 1) General Performance (http://stackoverflow.com/questions/356371/excel-interop-efficiency-and-performance) and 2) Empty Row - Deletion in this case, but you can use it to detect emptiness (http://stackoverflow.com/questions/9379673/excel-vba-delete-empty-rows) — Richard Morgan, Sep 03 '13 at 17:52
I assume there is a specific need for using C# or VBA iinstead of CountA (http://office.microsoft.com/en-us/excel-help/counta-HP005209027.aspx) — Ross Larson, Sep 03 '13 at 18:04

score 0 · Answer 1 · answered Sep 03 '13 at 18:05

I might not be understanding your question properly, but I'm guessing you're trying to find all the cells in column A that have a value in them and I'm assuming you're using Excel Interop in C#...

For that, you can use the Range.SpecialCells method.

So, for example, to get cells with constant values or formulas use:

worksheet.Range("A:A").SpecialCells(
    Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants |
    Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeFormulas)

That will return a range you can loop through and add to your list, a....

See the documentation here

Hope this helps...

Finding the number of cells in a row that has data

1 Answers1