Is there a LastPossibleRow property for VBA?

[MPastor]

For VBA applications using Excel 2000 and 2002:

I know that 65536 is currently the maximum possible row for an Excel sheet. I'm trying to avoid a problem if/when a future release comes out that allows more rows.

Rather than using .END(xlDown) and testing for the specific value 65536 to determine if the last possible row is the result, is there another way to test for LastPossibleRow??

I just know that if I "hardcode" the value 65536, I'll regret it months or years down the road.

Any alternatives or suggestions???

 

[MPastor]

Thank You --- to each and every one of you that replied and provided your suggestions. I greatly appreciate it!!!

Before posting this topic, I didn't know about UsedRange and the use of Activesheet.Cells.Rows.Count also provides some good food for thought.

Thank you all very much!!! (sorry, no champagne)

...Mike

 

[Tom Urtis]

I have to agree w/ Todd here. It's been my experience that UsedRange is more efficient and reliable.

Nope, very unreliable, as is xlCellTypeLastCell. See this for an example of how it can easily fail:
http://www.mrexcel.com/board2/viewtopic.php?t=96220&highlight=usedrange

The code line to accommodate future versions of greater row quantitities (probably not until at least 2009) is
Cells(rows.Count, 1).End(xlUp).Row
assuming column A.

Most reliable and consistent last row and last column variables:
Dim LR As Long, LC As Integer
LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LC = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

 

[Zack Barresse]

Thanks for that Tom. Your supreme expertise is yet again proven!! I guess what I should have mentioned was that my 'experience' was rather limited, and dull at that.

I really like that .Find method though. I've long been trying to strengthen my skills on that method but haven't mastered it yet. I do like it very much. Thank you!!

 

[MPastor]

Tom, Thank you!!! That's exactly what I was looking for.

...Mike

 

[Zack Barresse]

Yea!! Dk is going to be "drinking champagne and dancing the fandango"!

 

[dk]

Hey Zack,

What's the story then? Have they increased the number of rows? Any links confirming this? If so then I'd better get my webcam set up and polish my dancing shoes



Cheers
Dan

 

[DominicB]

Good morning MPastor

You're right to think about future-proofing your code. The rumour is that the latest Excel will have a grid size of 1,048,576 x 16,384.

HTH

DominicB

 

[dk]

Good news from http://blogs.msdn.com/excel/

The total number of available columns in Excel
Old Limit: 256 (2^8)
New Limit: 16k (2^14)

The total number of available rows in Excel
Old Limit: 64k (2^16)
New Limit: 1M (2^20)

and there's lots of other things to look forward to by the sound of it

Dan

 

[Todd Bardoni]

Wow, these are great too:-

Number of conditional format conditions on a cell
Old Limit: 3 conditions
New Limit: Limited by available memory

Number of items shown in the Auto-Filter dropdown
Old Limit: 1,000
New Limit: 10,000

But, I don't like this:-

The number of levels of nesting that Excel allows in formulas
Old Limit: 7
New Limit: 64

...leads to laziness and heading-spinning formulas.

 

[Zack Barresse]

Oh yeah, and the list goes on. :D

So, dk, river dance?