Excel Object Model: Worksheet.cells

Count rows, columns and cells in a worksheet

Because worksheet.cells returns the range for an entire Worksheet, we're able to display the maximum number of rows and columns, and by inference, the maximum number of cells, on a Worksheet:

option explicit

sub main() ' {

    dim allCells as range
    set allcells = activeSheet.cells()

    debug.print "The maximum number of rows in a worksheet is: " & allCells.rows.   count
    debug.print "The maximum number of cols in a worksheet is: " & allCells.columns.count



  ' Get maximum number of cells in an Excel Worksheet
  '
  '    Use countLarge because count throws
  '    a runtime error 6 (Overflow):
    dim maxCntOfCellsOnSheet as longLong
    maxCntOfCellsOnSheet = allCells.countLarge
    debug.print "This corresponds to  " & maxCntOfCellsOnSheet & " cells"

    debug.print "The address of 'all cells' is " & allCells.address

end sub ' }
'
' The maximum number of rows in a worksheet is: 1048576
' The maximum number of cols in a worksheet is: 16384
' This corresponds to  17179869184 cells
' The address of 'all cells' is $1:$1048576

Github repository about-MS-Office-object-model, path: /Excel/Worksheet/cells/numbers.bas

Select individual cells on a Worksheet

The range returned by worksheet.cells allows to select arbitrary cells on a worksheet. ….cells(R, C) returns a range that represents the cell at row R and column C. Because item is the default property of a Range object, ….cells(R, C) is equivalent to ….cells.item(R, C).

The following example chooses two cells that are used to define the upper left and bottom right cell of a range. Then, the cells in this range is assigned the value X:

public sub Main() ' {

    dim cur_worksheet as worksheet

    dim range_start   as range
    dim range_end     as range
    dim range_all     as range

    set cur_worksheet = activeSheet

 '  Note, instead of the explicit cur_worksheet.cells(...)
 '  the line can also be written just as
 '    cells(...) 
 '  without cur_worksheet.
    set range_start = cur_worksheet.cells( 4, 2) ' 4th row, 2nd column
    set range_end   = cur_worksheet.cells( 7, 5) ' 7th row, 5th column

    set range_all = range(range_start, range_end) 

    range_all.value = "X"

    activeWorkbook.saved = true

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/Worksheet/cells/select-cells.bas

Excel Object Model: Worksheet.cells

Count rows, columns and cells in a worksheet

Select individual cells on a Worksheet

See also