Determining the number of cells with data in a column
 

I know this has to be real simple, but I can't find an answer.

I have a column ("A") of data on a sheet ("OldFiles") and I just need to
know how many cells are in that column have data in them. The column is
sorted, so the non-blank cells will be contiguous and will be at the top of
the column.

TIA,
Ken

Determining the number of cells with data in a column
 

set rng = Cells(rows.count,1).End(xlup).Row

msgbox rng.row

--
Regards,
Tom Ogilvy

"Ken Loomis" wrote in message
...
I know this has to be real simple, but I can't find an answer.

I have a column ("A") of data on a sheet ("OldFiles") and I just need to
know how many cells are in that column have data in them. The column is
sorted, so the non-blank cells will be contiguous and will be at the top
of
the column.

TIA,
Ken

Determining the number of cells with data in a column
 

Try
num = worksheetfunction.counta(columns(1))

formula in worksheet =Counta(A:A)
--
steveB

Remove "AYN" from email to respond
"Ken Loomis" wrote in message
...
I know this has to be real simple, but I can't find an answer.

I have a column ("A") of data on a sheet ("OldFiles") and I just need to
know how many cells are in that column have data in them. The column is
sorted, so the non-blank cells will be contiguous and will be at the top
of the column.

TIA,
Ken

Determining the number of cells with data in a column
 

Here's where I always get so confused.

I think your suggested code, Tom, work with the active worksheet.

I was wanting to do that without selecting the worksheet. It's a hidden
worksheet.

So I tried this:

Set rng = Sheets("OldFiles").Cells(Rows.count, 1).End(xlUp).Row
FilesOnSheet = rng.Row


But with that, I get a:

Run-time error '13':
Type mismatch


What am I doing wrong and what am I not getting about this? Is it my lack of
understanding about the Object Model? And, if so, can someone point me in
the direction of something that will help me better understand that?

TIA,
Ken





"Tom Ogilvy" wrote in message
...
set rng = Cells(rows.count,1).End(xlup).Row

msgbox rng.row

--
Regards,
Tom Ogilvy

"Ken Loomis" wrote in message
...
I know this has to be real simple, but I can't find an answer.

I have a column ("A") of data on a sheet ("OldFiles") and I just need to
know how many cells are in that column have data in them. The column is
sorted, so the non-blank cells will be contiguous and will be at the top
of
the column.

TIA,
Ken

Determining the number of cells with data in a column
 

Well, that is my fault about the error:

Set rng = Sheets("OldFiles").Cells(Rows.count, 1).End(xlUp)
FilesOnSheet = rng.Row

or

lastrow = = Sheets("OldFiles").Cells(Rows.count, 1).End(xlUp).row

Sometime one changes their mind in the middle of writing the code.

--
Regards,
Tom Ogilvy


"Ken Loomis" wrote in message
...
Here's where I always get so confused.

I think your suggested code, Tom, work with the active worksheet.

I was wanting to do that without selecting the worksheet. It's a hidden
worksheet.

So I tried this:

Set rng = Sheets("OldFiles").Cells(Rows.count, 1).End(xlUp).Row
FilesOnSheet = rng.Row


But with that, I get a:

Run-time error '13':
Type mismatch


What am I doing wrong and what am I not getting about this? Is it my lack
of
understanding about the Object Model? And, if so, can someone point me in
the direction of something that will help me better understand that?

TIA,
Ken





"Tom Ogilvy" wrote in message
...
set rng = Cells(rows.count,1).End(xlup).Row

msgbox rng.row

--
Regards,
Tom Ogilvy

"Ken Loomis" wrote in message
...
I know this has to be real simple, but I can't find an answer.

I have a column ("A") of data on a sheet ("OldFiles") and I just need
to
know how many cells are in that column have data in them. The column is
sorted, so the non-blank cells will be contiguous and will be at the
top
of
the column.

TIA,
Ken

Determining the number of cells with data in a column
 

Thanks again, Tom.

As always, that worked great.

Ken


"Tom Ogilvy" wrote in message
...
Well, that is my fault about the error:

Set rng = Sheets("OldFiles").Cells(Rows.count, 1).End(xlUp)
FilesOnSheet = rng.Row

or

lastrow = = Sheets("OldFiles").Cells(Rows.count, 1).End(xlUp).row

Sometime one changes their mind in the middle of writing the code.

--
Regards,
Tom Ogilvy


"Ken Loomis" wrote in message
...
Here's where I always get so confused.

I think your suggested code, Tom, work with the active worksheet.

I was wanting to do that without selecting the worksheet. It's a hidden
worksheet.

So I tried this:

Set rng = Sheets("OldFiles").Cells(Rows.count, 1).End(xlUp).Row
FilesOnSheet = rng.Row


But with that, I get a:

Run-time error '13':
Type mismatch


What am I doing wrong and what am I not getting about this? Is it my lack
of
understanding about the Object Model? And, if so, can someone point me in
the direction of something that will help me better understand that?

TIA,
Ken





"Tom Ogilvy" wrote in message
...
set rng = Cells(rows.count,1).End(xlup).Row

msgbox rng.row

--
Regards,
Tom Ogilvy

"Ken Loomis" wrote in message
...
I know this has to be real simple, but I can't find an answer.

I have a column ("A") of data on a sheet ("OldFiles") and I just need
to
know how many cells are in that column have data in them. The column
is
sorted, so the non-blank cells will be contiguous and will be at the
top
of
the column.

TIA,
Ken