How to declare a variable for several subs
LastCell = Range("NameCopy")(Range("NameCopy").Count).Address
--
HTH
Bob Phillips
"David" wrote in message
...
Bob Phillips wrote
I'm back online, so I'll rejoin the discussion :-).
What you are doing looks workable (right or correct is subjective, so
I won't comment on that :-)). Just one point to pick up on though that
you mentioned. You said '... You must have read my mind, though,
because there *will* be occasions to expand the number of rows, which
is why I want to use rows.count rather than a specific number of rows
...'. Your approach does not protect you against that. For example,
say 'Name_Copy' refers to a range A1:M20, then rows.count returns 20,
and your variable will have 20 as its value. However, say a new row
gets inserted at row 10, then 'Name_Copy' will then refer to A1:M21,
but your variable will still have the value 20 in it. QED - a problem.
That is why it is sometimes better to recalculate such values just
prior to using them. Public variables can be good, but you still need
to know what is going on to use successfully.
So I found out. Routines bombed even after saving, closing and reopening
file after inserting rows. I've taken a different tack after reverting to
prior code.
Now...
I've discovered I can get the address of the first cell in a named range
with this: FirstCell=Range("<NamedRange").Cells(1).Address
This is useful in a .Resize line and to place text from my UserForm
Is there a way to reference the *last* cell in that same named range?
That would be useful in another Sub().
----
FirstCell = Range("Name_Copy").Cells(1).Address
LastCell = Range("Name_Copy").Cells(?).Address '<-- I want this!!
....
--
David
|