Counting cells with data in them
 

dim myRng as range
with activesheet
set myrng = .range("d1",.range("d1").end(xldown))
end with

msgbox application.counta(myrng)


This'll count everything--including formulas that evaluate to "".


Tony wrote:

Sorry if this post came up twice. I would like to know how to do a count
function on a selection of cells. My sheet has been filtered on this column
and then the following lines are used.

Range("d1").Select
Range(Selection, Selection.End(xlDown)).Select
sectornum = Selection.Count

This selects all the cells down to the last cell that has data in it but
then the variable is set to the total amount of cells selected. All I want to
know is how many cells have something in them. Thank you!

--

Dave Peterson

Counting cells with data in them
 

Good idea! I'd forgotten about CountA. But you need to use

Set myRng = .Range("D1", .Range("D65536").End(xlUp))

because

Set myRng = .Range("d1", .Range("d1").End(xlDown))

will stop at the first empty cell in Column D. I just tried it.

Regards,
Wes

Counting cells with data in them
 

True, but this apparently isn't an issue for the original poster:

Range(Selection, Selection.End(xlDown)).Select
This selects all the cells down to the last cell that has data in it but

I would guess that Dave tried it several years ago and is aware of the
difference.

--
Regards,
Tom Ogilvy


"SunTzuComm" wrote in message
...
Good idea! I'd forgotten about CountA. But you need to use

Set myRng = .Range("D1", .Range("D65536").End(xlUp))

because

Set myRng = .Range("d1", .Range("d1").End(xlDown))

will stop at the first empty cell in Column D. I just tried it.

Regards,
Wes

Counting cells with data in them
 

In fact, I changed my post to match the OP. 'Cause sometimes there's a reason
the OP used their code. <vbg.

Tom Ogilvy wrote:

True, but this apparently isn't an issue for the original poster:

Range(Selection, Selection.End(xlDown)).Select
This selects all the cells down to the last cell that has data in it but

I would guess that Dave tried it several years ago and is aware of the
difference.

--
Regards,
Tom Ogilvy

"SunTzuComm" wrote in message
...
Good idea! I'd forgotten about CountA. But you need to use

Set myRng = .Range("D1", .Range("D65536").End(xlUp))

because

Set myRng = .Range("d1", .Range("d1").End(xlDown))

will stop at the first empty cell in Column D. I just tried it.

Regards,
Wes

--

Dave Peterson