Determining the row limit in a macro

[Phil Hibbs]

How can a macro determine what the maximum number of rows is in the
version of Excel that is running? I don't want to just hard-code
version numbers, and I don't want to use .end(xldown) either.

Phil Hibbs.

 

[Rick Rothstein]

MaximumNumberOfRows = ActiveSheet.Rows.Count

 

[Phil Hibbs]

MaximumNumberOfRows = ActiveSheet.Rows.Count

Thanks. I can probably use that in most circumstances - the only one I
can think of that would be a problem is in the startup code of an add-
in, where there is no "ActiveSheet", but I don't actually need that,
I'm probably just being awkward by thinking of it.

Phil.

 

[Ryan H]

This will count the number of rows available in whichever Excel version you
use. Hope this helps! If so, let me know, click "YES" below.

Sub CountRows()

Dim NumberOfRows As Long

NumberOfRows = Rows.Count
MsgBox NumberOfRows

End Sub

 

[Jeff]

reurn max number of rows by version

Function MaxRow(ByVal Version As Double) As Long
Select Case Version
Case Is > 9
MaxRow = 65536
Case 10#
MaxRow = 65536
Case Is < 10
MaxRow = 1048576
End Select
End Function

 

[Peter T]

That function will return 65536 rows for all versions from Excel 2000, there
are several things wrong with it. You could perhaps do something like this -

If val(application.version) >=12 then
maxrows = 1048576
else
maxrows = 65536
end if.

However normally the programmer will want to know the number of rows in the
worksheet, which depending on the fileformat is not necessarily the same as
the maximum number of rows the version can handle.

Regards,
Peter T

 

[Peter T]

Thisworkbook.worksheets(1).rows.count

This should work from your addin's startup. However keep in mind the
possibility that in Excel 2007 you might be working with 1048576 rows and
old xls files with 65536 rows in the same session.

Regards,
Peter T

 

[Tom Ogilvy]

Jeff,

I printed out the results for versions 1 to 12.

1 1048576
2 1048576
3 1048576
4 1048576
5 1048576
6 1048576
7 1048576
8 1048576
9 1048576
10 65536
11 65536
12 65536

that doesn't seem correct to me. You must have something mixed up in your
UDF.

Just a heads up.

 

[Jeff]

thanks Tom

Function MaxRow(ByVal Version As Double) As Long
Select Case Version
Case Is > 9
MaxRow = 65536
Case 10#
MaxRow = 65536
Case Is > 10
MaxRow = 1048576
End Select
End Function

 

[Jeff]

Tom pointed out the typo in the UDF, The original post asked for the maximum
number of row by version.

How can a macro determine what the maximum number of rows is in the
version of Excel that is running? I don't want to just hard-code
version numbers, and I don't want to use .end(xldown) either.

Phil Hibbs.

 

[Rick Rothstein]

Still some problems with your code. First off, you need to specify
Application.Version and not just Version by itself for the object of the
Select Case statement. Second, any version greater than 9 (that is, 10, 11,
12, etc.) will be trapped by your first Case statement... you have to test
the higher numbered versions first. Also, did you separate Versions 9 and 10
instead of combining them a single Case statement (which, by the way, won't
be necessary once you reverse the order of the tests)? Also, so you don't
have to remember those large numbers, you can use powers of 2 instead...

Function MaxRow(ByVal Version As Double) As Long
Select Case Application.Version
Case Is > 10
MaxRow = 2 ^ 16 ' This equals 65536
Case Else
MaxRow = 2 ^ 20 ' This equals 1048576
End Select
End Function

 

[Ryan H]

Whats wrong with this?

Sub CountRows()

Dim NumberOfRows As Long

NumberOfRows = Rows.Count
MsgBox NumberOfRows

End Sub

 

[Peter T]

Rick, you might want to have a look at that again !

Regards,
Peter T

 

[Peter T]

I think we can ignore versions prior to 97 with only 16k rows, so we are
only need to test if Excel is version 12 (2007) or newer to know if it is
capable of handling 1048576 rows, if not the maximum is 65336. See the
simple example I posted in my previous reply to you.

I appreciate the OP asked about maximum rows by version, even so I thought
worthwhile to mention it is normally the workbook in question that needs to
be considered, rather than the Excel version.

Regards,
Peter T

 

[Rick Rothstein]

As Peter pointed out, I have the number of rows reversed...

Function MaxRow(ByVal Version As Double) As Long
Select Case Application.Version
Case Is > 10
MaxRow = 2 ^ 20 ' This equals 1048576
Case Else
MaxRow = 2 ^ 16 ' This equals 65536
End Select
End Function

 

[Rick Rothstein]

LOL... do I get points for being close? <g>

My wife has been using the computer a lot today and I had a few minutes to
sneak in while she had gotten up... I guess I rushed it just a little too
much, eh?<g> I just posted a correction against my message.

 

[Peter T]

I guessed it was the wife's fault, or something like that <g>

Regards,
Peter T

 

[Peter T]

Still a typo, which I think can be fairly blamed on trying to correct Jeff's
<g>

change
Case Is > 10
to
Case Is > 11 ' later than xl2003
or
Case Is >= 12

Peter T

 

[Phil Hibbs]

This should work from your addin's startup. However keep in mind the
possibility that in Excel 2007 you might be working with 1048576 rows and
old xls files with 65536 rows in the same session.

Ah, I didn't realise that. Thanks. I will need to treat it as a
workbook property and not an applicaton property then.

Phil Hibbs.