RANGE("A65536")

[jason]

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

 

[Chip Pearson]

Jason,

Try

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

 

[John Wilson]

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

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]

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

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

 

[J.E. McGimpsey]

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

 

[John Wilson]

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

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

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

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

 

[Aaron Queenan]

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

 

[Myrna Larson]

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

 

[John Wilson]

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

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

 

[jason]

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

 

[Jim Carlock]

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>

 

[jason]

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

 

[Tom Ogilvy]

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

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]

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

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

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

 

[John Wilson]

jason,

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

John

 

[Dana DeLouis]

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>

 

[jason]

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]

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

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>