Rows in a Range

[John Smith]

I have selected a column and called it "Mark" I need to determine the number
of rows in this column with values in them at runtime. The number of rows
used in this column will vary.

How do I determine the number of rows with values?

If I use Range("Mark").Rows.Count it appears to return a very large number.

thank you
mark

 

[keepITcool]

This would get you the count all filled cells in the range
application.countA(Range("mark"))
This would get you the count all NUMERIC cells in the range
application.count(Range("mark"))

Notes:
VBA
Range("mark") is unqualified, thus works on the activeworkbook only.
VBA
application.countA is preferred notation for
application.worksheetfunction.countA
(you dont get intellisense, but you dont get runtime errors either)

NAME object
when you use names be aware of the differences in using "local" and
"global" names. If your sheet may be copied by users IMO it's better to
use "local" names only.

You'll define "local" names by adding the sheet's name and an exclamation
mark to them in the define name dialog. e.g. Sheet1!mark

 

[John Smith]

thank you
mark

This would get you the count all filled cells in the range
application.countA(Range("mark"))
This would get you the count all NUMERIC cells in the range
application.count(Range("mark"))

Notes:
VBA
Range("mark") is unqualified, thus works on the activeworkbook only.
VBA
application.countA is preferred notation for
application.worksheetfunction.countA
(you dont get intellisense, but you dont get runtime errors either)

NAME object
when you use names be aware of the differences in using "local" and
"global" names. If your sheet may be copied by users IMO it's better to
use "local" names only.

You'll define "local" names by adding the sheet's name and an exclamation
mark to them in the define name dialog. e.g. Sheet1!mark





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

John Smith wrote in message

 

[Bob Phillips]

See response to original question.

--

HTH

RP
(remove nothere from the email address if mailing direct)