Home >> Computing >> VBA

Finding the real last cell in a spreadhseet.

Method 1 (unreliable)

The standard method for determining the last row/column in an Excel spreadsheet is:

num_rows = ws.Range("A1").SpecialCells(xlLastCell).Row

Unfortunately, the result obtained is often wrong. Excel remembers the highest row/column used for a worksheet; but it does not reset it to a lower value if the extreme cells are emptied. To overcome this problem, you must adopt the method below

Method 2 (reliable)

Public Function NumRows(ws)
'return the number of rows in a worksheet

    NumRows = 0
    On Error Resume Next
    NumRows = ws.Cells.Find("*", ws.Range("A1"), _
        xlFormulas, , xlByRows, xlPrevious).Row
   On Error GoTo 0
End Function

Public Function NumCols(ws)
'return the number of columns in a worksheet

    NumCols = 0
    On Error Resume Next
    NumCols = ws.Cells.Find("*", ws.Range("A1"), _
        xlFormulas, , xlByColumns, xlPrevious).Column
   On Error GoTo 0
   
End Function