[.net] How to count the number of rows in excel with data?

column A has data like this (ie frequent blank cells):

HEADING  <-- this is A1
kfdsl
fdjgnm
fdkj

gdfkj
4353

fdjk  <-- this is A9

I would like to be able to get the cell reference of the last cell that has data. So in the above example, I want to return: A9

I have tried this but it stops at the first blank cell (ie returning A4)

numofrows = destsheet.Range("A2").End(xlDown).Row - 1

Safest option is

Lastrow =  Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
Lastcol =  Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

Don't use UsedRange or SpecialCells(xlLastCell) or End(xlUp). All these methods may give wrong results if you previously deleted some rows. Excel still counts these invisible cells.

These methods will work again if you delete your cells, save the workbook, close and re-open it.

These would both work as well, letting Excel define the last time it sees data

numofrows = destsheet.UsedRange.SpecialCells(xlLastCell).row

numofrows = destsheet.Cells.SpecialCells(xlLastCell).row

This will work, independent of Excel version (2003, 2007, 2010). The first has 65536 rows in a sheet, while the latter two have a million rows or so. Sheet1.Rows.Count returns this number dependent on the version.

numofrows = Sheet1.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row

or the equivalent but shorter

numofrows = Sheet1.Cells(Sheet1.Rows.Count,1).End(xlUp)

This searches up from the bottom of column A for the first non-empty cell, and gets its row number.

This also works if you have data that go further down in other columns. So for instance, if you take your example data and also write something in cell FY4763, the above will still correctly return 9 (not 4763, which any method involving the UsedRange property would incorrectly return).

Note that really, if you want the cell reference, you should just use the following. You don't have to first get the row number, and then build the cell reference.

Set rngLastCell = Sheet1.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp)

Note that this method fails in certain edge cases:

• Last row contains data
• Last row(s) are hidden or filtered out

So watch out if you're planning to use row 1,048,576 for these things!

I prefer using the CurrentRegion property, which is equivalent to Ctrl-*, which expands the current range to its largest continuous range with data. You start with a cell, or range, which you know will contain data, then expand it. The UsedRange Property sometimes returns huge areas, just because someone did some formatting at the bottom of the sheet.

Dim Liste As Worksheet    
Set Liste = wb.Worksheets("B Leistungen (Liste)")     
Dim longlastrow As Long
longlastrow = Liste.Range(Liste.Cells(4, 1), Liste.Cells(6, 3)).CurrentRegion.Rows.Count

For greater clarity, I want to add a clear example and running

            openFileDialog1.FileName = "Select File"; 
            openFileDialog1.DefaultExt = ".xls"; 
            openFileDialog1.Filter = "Excel documents (.xls)|*.xls"; 


            DialogResult result = openFileDialog1.ShowDialog();


            if (result==DialogResult.OK)
            {

                string filename = openFileDialog1.FileName;


                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;

                xlApp = new Excel.Application();
                xlApp.Visible = false;
                xlApp.DisplayAlerts = false;



                xlWorkBook = xlApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                var numRows = xlWorkSheet.Range["A1"].Offset[xlWorkSheet.Rows.Count - 1, 0].End[Excel.XlDirection.xlUp].Row;

                MessageBox.Show("Number of max row is : "+ numRows.ToString());

                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

            }

Dim RowNumber As Integer
RowNumber = ActiveSheet.Range("A65536").End(xlUp).Row

In your case it should return #9

Found this approach on another site. It works with the new larger sizes of Excel and doesn't require you to hardcode the max number of rows and columns.

iLastRow = Cells(Rows.Count, "a").End(xlUp).Row
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column

Thanks to mudraker in Melborne, Australia

  n = ThisWorkbook.Worksheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count

I like this way:

ActiveSheet.UsedRange.Rows.Count

The same can be done with columns count. For me, always work. But, if you have data in another column, the code above will consider them too, because the code is looking for all cell range in the sheet.

I compared all possibilities with a long test sheet:

0,140625 sec for

lastrow = calcws.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).row

0 sec for

iLastRow = calcws.Cells(rows.count, "a").End(xlUp).row

and

numofrows = calcws.Cells.SpecialCells(xlLastCell).row

0,0078125 sec for

lastrow = calcws.UsedRange.rows.count
Do While 1
    If calcws.Cells(lastrow, 1).Value = "" Then
        lastrow = lastrow - 1
    Else
        Exit Do
    End If
Loop

I think the favourites are obvious...