Constant for Maximum Rows(65536) - is there one? 2

[nodrog77]

Just thought I'd ask before I put this:

Public Const XlMaxRows As Long = 65536

In the various bits of code I have. I figured surely they would for backward/forward compatibility.

Or am I just Fantasizing ??

 

[GlennUK]

I use Cells.Rows.Count in my code, if I ever need to code for max rows ... but I hardly ever do that ( I tend to use UsedRange more than anything else ).

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[nodrog77]

Glenn,

Yeah I thought of using UsedRange until I read that it wasn't 100% reliable in faq707-2112 .

I'm not sure what they mean by "not 100% reliable" but this little bit of code I'm doing has to be and it's on excel 2003 so I sorta don't want to use it.

Cells.Rows.Count sounds feasible, I'll give it a try,
Thanks.

 

[combo]

You can replace constant by r/o property or function, both can be in standard module:

Public Property Get xlMaxRows() As Long
Select Case Val(Application.Version)
Case Is < 8
    xlMaxRows = 16384
Case 8 To 11
    xlMaxRows = 65536
Case 12
xlMaxRows = 1048576
End Select
End Property

Sub TestMyExcel()
MsgBox xlMaxRows
End Sub

combo

 

[strongm]

Application.Rows.Count

 

[combo]

If no worksheet specified, the ...Rows.Count fails if a chart sheet is active or a chart object on the worksheet is selected. Why count rows when the size of worksheet is defined and depend only on the excel version?

combo

 

[SkipVought]

Public Property Get xlMaxRows() As Long
    xlMaxRows = thisworkbook.sheets(1).rows.count
End Property

Sub TestMyExcel()
MsgBox xlMaxRows
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

Just for fun: there can be a workbook without worksheets or with the first sheet other than worksheet...

combo

 

[strongm]

>Why count rows when the size of worksheet is defined and depend only on the excel version?

Because MS might release a patch that increases the number of rows. Or a new version of Excel with a different number of rows. So code that doesn't know about the patch or the new version would get it wrong. Hence nodrogs comment about forward compatibility

 

[SkipVought]

strongm, sweet 'n' simple! ==> [purple]*[/purple]

Didn't realize that the Application object has the rows property.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}