number of non-empty rows on a worksheet

[jonne]

Hi all,

With a loop like this:

Do While ActiveCell <> "" 'Loops until the active cell is blank.
...
ActiveCell.Offset(1, 0).Select
Loop

one can loop through a worksheet column until a cell is blank.

Is it also to possible to loop until the end (I mean, to continue even
when several cells are empty but non-emtpy cells are still present)??
(I would like to obtain the number of non-empty cells, or the index
of the last non-empty cell, but don't know whether such a function exists).

Thanks in advance,
Jonne.

 

[Guest]

You can use SpecialCells to find the last used cell in any range:

Set LastCellInC = Range("C:C").SpecialCells(xlCellTypeLastCell)
UsedRowsInC = LastCellInC.Row

 

[Tom Ogilvy]

Set LastCellInC = Range("C:C").SpecialCells(xlCellTypeLastCell)

will give you the last used cell regardless of which column it is in

for a specific column

Set LastCelInC = cells(rows.count,3).End(xlup)

demo'd from the immediate window:

Range("C:C").SpecialCells(xlCellTypeLastCell).Select
? selection.Address
$E$10
? cells(rows.Count,3).End(xlup).address
$C$9

if you want to count non-empty cells

cnt = Application.countA(columns(3))