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

 

[BJungheim]

range("A65536").end(xlup).row

Where A is the column you want to test

 

[MPastor]

BJungheim, I guess I wasn't clear. I'm looking for some other way to "derive" Last Possible Row number, INSTEAD of hardcoding the current maximum row value of 65,536. I don't see how your suggestion avoids hardcoding the 65536 value.

 

[Smitty]

The SpecialCells method may work for you:

I.E.
<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> LastRow()
    MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Hope that helps,

Smitty

 

[Todd Bardoni]

Or use UsedRange

 

[Zack Barresse]

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

Sub TheEnd()
    MsgBox ActiveSheet.UsedRange.Address
End Sub

(Hi fellas! )

 

[Todd Bardoni]

i = Sheets("Sheet1").UsedRange.Rows.Count

will give you the last row number. You can use offset to get the one below that.

heya zack and smitty!

edit:- It's only accurate if you have a header or something in row 1.

 

[dk]

Activesheet.Cells.Rows.Count will return the total number of rows available. If Microsoft actually do eventually increase the number of rows then you will see me drinking champagne and dancing the fandango.

HTH
Dan

 

[dk]

Activesheet.Cells.Rows.Count will return the total number of rows available. You will see me drinking light beer before Microsoft actually do increase the number of rows.

HTH
Dan

 

[Zack Barresse]

Hey Dan,

I heard a rumor somewhere (in chatting) that xl2005 is going to have increased row/column capacity. Have you heard of that also? Now that would be cool. Will you do a web cam of your fandango and champ/beer?