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...

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

"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

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

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...

"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!

Would have been good (I'm nosey!)
Anyway my mind's at rest now
Thanks

"J.E. McGimpsey" wrote in message ...
Not secret, and not possible.

When you enter a password, it's mathematically manipulated
("hashed") into a string of bits that's the equivalent of 12 8-bit
characters, the first 11 of which have one of two values. Nothing is
left of the original password, nor is the original password stored
anywhere.

When you enter a password, it is put through the same hash routine
and compared to the stored hash. If it's the same, protection is
removed.

There are a maximum of 194,000 possible hash values, and 5.46109E+23
possible 1-12 character passwords, using only the 95 ASCII
characters that XL uses for the last hash character, so that there
are, in theory, a very large number of character strings that will
work just as well as the original. However, it's still very unlikely
that anyone will randomly choose/guess a string that hashes to a
match. But for the computer, running through those possibilities is
trivial, which is why the password cracker works quickly.

It might, if the exact hash routine were known, be possible to
reverse it and come up with thousands (5-characters) to
quadrillions (12-characters) of character strings, one of which
would be the original password. But then you'd have to wade through
them to find something that looked right - and you'd never be sure
(unless you recognized it) that it was the original.


In article ,
(jason) wrote:

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

Although slower

Not really

Sub Tester2()
Dim sngStart As Single
Dim i As Long
Dim rng As Range
sngStart = Timer
For i = 1 To 1000000
Set rng = [A1]
Next
Debug.Print "Brackets " & Timer - sngStart
End Sub

Sub Tester3()
Dim sngStart As Single
Dim i As Long
Dim rng As Range
sngStart = Timer
For i = 1 To 1000000
Set rng = Application.Evaluate("A1")
Next
Debug.Print "Evaluate " & Timer - sngStart
End Sub

Sub Tester4()
Dim sngStart As Single
Dim i As Long
Dim rng As Range
sngStart = Timer
For i = 1 To 1000000
Set rng = Range("A1")
Next
Debug.Print "Range " & Timer - sngStart
End Sub

Brackets 7.742188
Brackets 7.6875
Brackets 7.6875
Evaluate 7.796875
Evaluate 7.804688
Evaluate 7.859375
Range 2.09375
Range 2.085938
Range 2.085938

But both are **much slower** than "normal" range notation.

--
Regards,
Tom Ogilvy

Dana DeLouis wrote in message
...
I don't believe it will work like this because it can't join strings and
"evaluate" it at the same time.
Although slower, I believe you are trying to do something like this...

Application.Goto Evaluate("A" & Rows.Count)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


How come it is not possible to use square brackets in a case like
this, why doesn't [A & Rows.Count].Select work?

Jason

Tom,

Look!!
Brackets 13.51563
Evaluate 13.8125
Range 3.703125
I think i need a faster machine!
I still just like the look of the square brackets (seem tidier somehow).
I don't have too many million long loops, thank god.
Cool way of assessing it though (I may adapt the code to compare other stuff)

Thanks again
Jason

"Tom Ogilvy" wrote in message ...
Although slower

Not really

Sub Tester2()
Dim sngStart As Single
Dim i As Long
Dim rng As Range
sngStart = Timer
For i = 1 To 1000000
Set rng = [A1]
Next
Debug.Print "Brackets " & Timer - sngStart
End Sub

Sub Tester3()
Dim sngStart As Single
Dim i As Long
Dim rng As Range
sngStart = Timer
For i = 1 To 1000000
Set rng = Application.Evaluate("A1")
Next
Debug.Print "Evaluate " & Timer - sngStart
End Sub

Sub Tester4()
Dim sngStart As Single
Dim i As Long
Dim rng As Range
sngStart = Timer
For i = 1 To 1000000
Set rng = Range("A1")
Next
Debug.Print "Range " & Timer - sngStart
End Sub

Brackets 7.742188
Brackets 7.6875
Brackets 7.6875
Evaluate 7.796875
Evaluate 7.804688
Evaluate 7.859375
Range 2.09375
Range 2.085938
Range 2.085938

But both are **much slower** than "normal" range notation.

--
Regards,
Tom Ogilvy

Dana DeLouis wrote in message
...
I don't believe it will work like this because it can't join strings and
"evaluate" it at the same time.
Although slower, I believe you are trying to do something like this...

Application.Goto Evaluate("A" & Rows.Count)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


How come it is not possible to use square brackets in a case like
this, why doesn't [A & Rows.Count].Select work?

Jason

jason,

Don't feel bad.........
Brackets 66.83984
Evaluate 67.17188
Range 17.35938

John

jason wrote:

Tom,

Look!!
Brackets 13.51563
Evaluate 13.8125
Range 3.703125
I think i need a faster machine!
I still just like the look of the square brackets (seem tidier somehow).
I don't have too many million long loops, thank god.
Cool way of assessing it though (I may adapt the code to compare other stuff)

Thanks again
Jason

Hi Jason. I guess I didn't say that quite right. I was referring to your
use of joining two items within [ ]. I believe you wanted to use [ ] where
the data within was not fixed.
You were using [A & Rows.Count] vs a hard-wired [A65535] or [A1].
With a separate reference to the Row & Column, I was just mentioning that it
would probable be faster to use something like
Cells(Rows.Count, "A").Select (or Cells(Rows.Count, 1).Select)

Just for fun...if you really wanted to join two strings, and then use [ ]
for looks, this is just one option.

ActiveWorkbook.Names.Add "GoTo", "A" & Rows.Count
[Indirect(Goto)].Select

You can get as fancy as you want with a range-named-formula using
"Offset( )"
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

<snip

Nice one Dana,
I quite like:

TheEnd = Cells(Rows.Count, 1).Row
Cells(TheEnd, 1).Select

no square brackets but looks sort of good
J

"Dana DeLouis" wrote in message ...
Hi Jason. I guess I didn't say that quite right. I was referring to your
use of joining two items within [ ]. I believe you wanted to use [ ] where
the data within was not fixed.
You were using [A & Rows.Count] vs a hard-wired [A65535] or [A1].
With a separate reference to the Row & Column, I was just mentioning that it
would probable be faster to use something like
Cells(Rows.Count, "A").Select (or Cells(Rows.Count, 1).Select)

Just for fun...if you really wanted to join two strings, and then use [ ]
for looks, this is just one option.

ActiveWorkbook.Names.Add "GoTo", "A" & Rows.Count
[Indirect(Goto)].Select

You can get as fancy as you want with a range-named-formula using
"Offset( )"

Just be aware that it might be more efficient to use...
TheEnd = Rows.Count

instead of ...
TheEnd = Cells(Rows.Count, 1).Row


Just for a fun side track, it sounds like you may find this interesting...
:)

Sub Demo()
'// Dana DeLouis

[D3].Select

' If you are in D3, and want to go to the bottom of the current column
' it is easier to use this...

Cells(Rows.Count, ActiveCell.Column).Select

' However, if you like to use [ ], here is just one of a few ways...
' Thought you might like this... :)

ActiveWorkbook.Names.Add _
"BottomRow", _
Replace("=ADDRESS(#,COLUMN(),2)", "#", Rows.Count)

'Top
[B1].Select
'Bottom
Range([BottomRow]).Select

'Top
[D1].Select
'Bottom
Range([BottomRow]).Select
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"jason" wrote in message
om...
Nice one Dana,
I quite like:

TheEnd = Cells(Rows.Count, 1).Row
Cells(TheEnd, 1).Select

no square brackets but looks sort of good
J

<snip