A small footnote, Rows.Count will refer to the activesheet, which is not a
problem unless your project includes chart sheets. Then you'll want to
qualify it

Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp)



"JLatham" wrote:

Bob,
Thanks a TON! for that - actually I hadn't ever used .End all that much at
all until stumbling into the forums here and found it being used as I
referenced it earlier, I actually cannot recall an instance of seeing it used
with the constant. What I learned about it I learned in these forums and I
won't mention any names (although I doubt the list would include yours). I
very much appreciate your response to this.

I concur: program it right and it'll last longer. I also say - teach it
right and fewer bad habits will get passed along to future generations.

"Bob Phillips" wrote:


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
I think we're going to need a bigger computer.

16K * 1M = 16GB just to hold one sheet full of nothing but a single
character in each cell. That's besides overhead to define the sheet and
addresses involved.


Bad practices will create problems in the future just as they always have.


But the addition of more rows was needed, and there have been situations
in
the past when 256 columns wasn't enough - mostly due to lack of foresight
on
my part, and having to transform all the data on a sheet because changes
made
long after the initial design ran me out of columns.

Yes, especially for a whole years of daily data, but did we really need 16K
and 1M?


One thing I've been wondering about is how the extended row numbers will
affect 'standard practice' functions like =Range("A65536").End(xlUp) - are
all of those going to have to change to Range("A1000000").End(xlUp) or I'm
hoping they will provide a constant that is Excel version-conscious to use
to
reference the highest numbered row on a sheet, maybe like Range(A &
xlLastPossibleRow).End(xlUp), where value of xlLastPossibleRow is
determined
by version of Excel.


If you had coded well, you would not have a problem. There has always been a
constant

Range("A" & Rows.Count).End(xlUp)

is future-proof.

Or you could use SpecialCells(xlCellTypeLastCell), now and in future.