number of non-empty rows on a worksheet
 

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.

number of non-empty rows on a worksheet
 

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

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

"jonne" wrote:

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.

number of non-empty rows on a worksheet
 

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).Sele ct
? 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))

--
Regards,
Tom Ogilvy



"K Dales" wrote in message
...
You can use SpecialCells to find the last used cell in any range:

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

"jonne" wrote:

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.