Using COUNT value as variable
I have a named range called "lists" in a single column on a worksheet.
Here is a demo from the immediate window:
Worksheets("Sheet1").Range("B10:B200").name = "Lists"
? activeworkbook.names("Lists").RefersTo
=Sheet1!$B$10:$B$200
x = Range("Lists").Rows.Count
? x
191
Works fine for me. Perhaps you haven't communicated your situation
correctly.
--
Regards,
Tom Ogilvy
"Naterator" wrote in message
...
Tom,
Thanks for giving me a hand. When i used that code I immediately got a
runtime error '424' : Object required. What's going on with that?
Thanks again,
Nate
"Tom Ogilvy" wrote:
x = Range("Lists").Rows.count
if this code is in a sheet module and Lists is on another sheet or Lists
is
a sheet level name (than actually Sheet1!Lists as an example), it will
need
to be qualified with the sheet name where it exits
x = Worksheets("sheet1").Range("Lists").Rows.count
Actually hopping back and forth is generally counter productive and
almost
never necessary.
--
Regards,
Tom Ogilvy
"Naterator" wrote:
I have a few named ranges on various worksheets. I can use VBA to hop
back
and forth between these sheets with no difficulty. Now, I need my
program to
count the cells in a named range and then store that value as a
variable.
Example:
I have a named range called "lists" in a single column on a worksheet.
I
need to engage a For loop that uses the number of cells in "lists" as
its
upper bound. i.e. say "lists" contains 92 cells I need a line of
code that
counts the number of cells in "lists" (92) and then sets x = 92. The
size of
"lists" is not static, thus i can't just use the number 92; I actually
need
the value returned by the count function to be stored as x for later
use in a
for loop.
Any help would be greatly appreciated.
Thank you very much,
Nate
|