Without having to always remember the row number 65536 how do I
quickly reference the last row in a worksheet.This has to work even if
there is a load of tables all over the sheet i.e [A1].end(xldown) will
not work!

Cheers
Jason

Jason,

Try

Cells(Rows.Count, "A").End(xlUp).Select
'or
ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Select

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com



"jason" wrote in message
om...
Without having to always remember the row number 65536 how do I
quickly reference the last row in a worksheet.This has to work even if
there is a load of tables all over the sheet i.e [A1].end(xldown) will
not work!

Cheers
Jason

jason,

Without having to always remember the row number 65536
Eventually, you cross a threshold where you never have to
"remember" it. It'll be permanently burned into your memory
and from that point on you'll never be able to forget it.

Aside from that, Chip's answers work very well.

John

"jason" wrote in message
om...
Without having to always remember the row number 65536 how do I
quickly reference the last row in a worksheet.This has to work even if
there is a load of tables all over the sheet i.e [A1].end(xldown) will
not work!

Cheers
Jason

"John Wilson" wrote:
Eventually, you cross a threshold where you never have to
"remember" it. It'll be permanently burned into your memory
and from that point on you'll never be able to forget it.

Until the number of rows hits the 32-bit limit, then the number
will be some number between:

65,536 and 4,294,967,296

;-)

With the way memory prices are dropping and Microsoft
heading into the 64-bit operating system being the standards, I'm
guessing another 2 years and everything might all be 32-bit or
heaven forsakes!!! 64-bit!!! LOL That'll drive everyone nuts!

Can you count to 2^64 ?

I'm still working on trying to remember...

Four-billion, two-hundred ninety-four million, nine-hundred
sixty-seven thousand, two-hundred ninety-six!

Motherboards are already being built to handle 1GB of memory
and more. Geesh. I can remember being thrilled by a 1GB hard
disk drive and then reading that some nerd that worked for PC
Magazine had a 386DX with a machine with 1GB of RAM and
I was using either a NorthGate 286-12Mhz with 1MB of RAM or a
386sx-20MHz. BTW, I kept the NorthGate 286 around until
486 machines started coming out because it worked as well or
better than most of the first 486 machines that hit the market.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


jason,

Without having to always remember the row number 65536

Aside from that, Chip's answers work very well.

John

"jason" wrote in message
om...
Without having to always remember the row number 65536 how do I
quickly reference the last row in a worksheet.This has to work even if
there is a load of tables all over the sheet i.e [A1].end(xldown) will
not work!

Cheers
Jason

In article ,
"Jim Carlock" wrote:

With the way memory prices are dropping and Microsoft
heading into the 64-bit operating system being the standards, I'm
guessing another 2 years and everything might all be 32-bit or
heaven forsakes!!! 64-bit!!! LOL That'll drive everyone nuts!

I'd bet a whole bunch that you'll still only have to remember 2^16 =
65536 for XL rows...

Thats where the 65536 comes from (learn something new everyday)

How do I convert the password that is churned out from the password
cracker into English - or is it:
A. A secret
B. Not possible

Jason

"J.E. McGimpsey" wrote in message ...
In article ,
"Jim Carlock" wrote:

With the way memory prices are dropping and Microsoft
heading into the 64-bit operating system being the standards, I'm
guessing another 2 years and everything might all be 32-bit or
heaven forsakes!!! 64-bit!!! LOL That'll drive everyone nuts!

I'd bet a whole bunch that you'll still only have to remember 2^16 =
65536 for XL rows...

Jim,

From what I understand, that 65536 limitation will still be there
in version 11 (Excel 2003), so don't hold your breath.

I'm still working on trying to remember...
You'll have a few more years to practice before it changes, so
not to worry <vbg

Geesh. I can remember being thrilled by a 1GB hard
disk drive
I was equally thrilled when I got a tape drive for my VIC-20.

Extending that 65536 limitation is probably one of the most
"asked for" features. Judging by the number of ng posts
on the subject of that limitation, many people run into that
same brick wall. I use Excel for a lot of things that would
probably be better suited in Access but with memory and
processor speed on the rise, I can still get away with it.
I can only assume that without that limitation, there'd be
less of a market for Access, so why would MS want to
spend money to enhance one product that might lessen
the need to purchase another??

John



"Jim Carlock" wrote in message
...
"John Wilson" wrote:
Eventually, you cross a threshold where you never have to
"remember" it. It'll be permanently burned into your memory
and from that point on you'll never be able to forget it.

Until the number of rows hits the 32-bit limit, then the number
will be some number between:

65,536 and 4,294,967,296

;-)

With the way memory prices are dropping and Microsoft
heading into the 64-bit operating system being the standards, I'm
guessing another 2 years and everything might all be 32-bit or
heaven forsakes!!! 64-bit!!! LOL That'll drive everyone nuts!

Can you count to 2^64 ?

I'm still working on trying to remember...

Four-billion, two-hundred ninety-four million, nine-hundred
sixty-seven thousand, two-hundred ninety-six!

Motherboards are already being built to handle 1GB of memory
and more. Geesh. I can remember being thrilled by a 1GB hard
disk drive and then reading that some nerd that worked for PC
Magazine had a 386DX with a machine with 1GB of RAM and
I was using either a NorthGate 286-12Mhz with 1MB of RAM or a
386sx-20MHz. BTW, I kept the NorthGate 286 around until
486 machines started coming out because it worked as well or
better than most of the first 486 machines that hit the market.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


jason,

Without having to always remember the row number 65536

Aside from that, Chip's answers work very well.

John

"jason" wrote in message
om...
Without having to always remember the row number 65536 how do I
quickly reference the last row in a worksheet.This has to work even if
there is a load of tables all over the sheet i.e [A1].end(xldown) will
not work!

Cheers
Jason

IMO, if you need a relational database, Excell won't cut it, regardless of the number of rows.


On Mon, 1 Sep 2003 11:58:20 -0400, "John Wilson" wrote:
less of a market for Access

Myrna,

if you need a relational database, Excell won't cut it
Agreed.
There are a lot of people who have had to take the Access route
strictly because of that row limitation though and I'm sure that
many of them wouldn't have done so if all their data could have fit on
a single sheet.

John

"Myrna Larson" wrote in message
...
IMO, if you need a relational database, Excell won't cut it, regardless of
the number of rows.


On Mon, 1 Sep 2003 11:58:20 -0400, "John Wilson"
wrote:
less of a market for Access

"Jim Carlock" wrote in message
...
"John Wilson" wrote:
Motherboards are already being built to handle 1GB of memory
and more. Geesh. I can remember being thrilled by a 1GB hard
disk drive and then reading that some nerd that worked for PC
Magazine had a 386DX with a machine with 1GB of RAM and
I was using either a NorthGate 286-12Mhz with 1MB of RAM or a
386sx-20MHz. BTW, I kept the NorthGate 286 around until
486 machines started coming out because it worked as well or
better than most of the first 486 machines that hit the market.

Ah... ye younguns... :-)

30MB disk drive was massive when I started on PCs, but now I probably have
more memory in my watch. And I'm only 21 (hexadecimal). :-D

"Aaron Queenan" wrote:
Ah... ye younguns... :-)

30MB disk drive was massive when I started on PCs, but now I probably have
more memory in my watch. And I'm only 21 (hexadecimal). :-D


I didn't want to mention the Atari 400 with the Cassette Tape or
the Atari 2600 (game machine). The 286 only had a 65MB HDD on
it. Prior to the 286, I used the first c.k.m.

<gulp

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!