Determining the number of cells with data in a column

[Ken Loomis]

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

 

[Tom Ogilvy]

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

msgbox rng.row

 

[STEVE BELL]

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

formula in worksheet =Counta(A:A)

 

[Ken Loomis]

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]

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.

 

[Ken Loomis]

Thanks again, Tom.

As always, that worked great.

Ken