Home >> Computing >> VBA
Finding the real last cell in a spreadhseet.
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
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