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.

MaximumNumberOfRows = ActiveSheet.Rows.Count

--
Rick (MVP - Excel)


"Phil Hibbs" wrote in message
...
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 wrote:
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.

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
--
Cheers,
Ryan


"Phil Hibbs" wrote:

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

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

"Ryan H" wrote:

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
--
Cheers,
Ryan


"Phil Hibbs" wrote:

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

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

"Jeff" wrote in message
...
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

"Ryan H" wrote:

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
--
Cheers,
Ryan


"Phil Hibbs" wrote:

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

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


"Phil Hibbs" wrote in message
...
Rick Rothstein wrote:
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.

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.

--
Regards,
Tom Ogilvy


"Jeff" wrote:

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

"Ryan H" wrote:

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
--
Cheers,
Ryan


"Phil Hibbs" wrote:

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

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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Jeff" wrote:

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

"Ryan H" wrote:

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
--
Cheers,
Ryan


"Phil Hibbs" wrote:

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

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.


"Peter T" wrote:

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

"Jeff" wrote in message
...
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

"Ryan H" wrote:

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
--
Cheers,
Ryan


"Phil Hibbs" wrote:

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



.

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

--
Rick (MVP - Excel)


"Jeff" wrote in message
...
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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Jeff" wrote:

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

"Ryan H" wrote:

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
--
Cheers,
Ryan


"Phil Hibbs" wrote:

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

Whats wrong with this?

Sub CountRows()

Dim NumberOfRows As Long

NumberOfRows = Rows.Count
MsgBox NumberOfRows

End Sub

--
Cheers,
Ryan


"Jeff" wrote:

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.


"Peter T" wrote:

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

"Jeff" wrote in message
...
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

"Ryan H" wrote:

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
--
Cheers,
Ryan


"Phil Hibbs" wrote:

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, you might want to have a look at that again !

Regards,
Peter T

"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)


"Jeff" wrote in message
...
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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Jeff" wrote:

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

"Ryan H" wrote:

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
--
Cheers,
Ryan


"Phil Hibbs" wrote:

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

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


"Jeff" wrote in message
...
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.


"Peter T" wrote:

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

"Jeff" wrote in message
...
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

"Ryan H" wrote:

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
--
Cheers,
Ryan


"Phil Hibbs" wrote:

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



.

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 (MVP - Excel)


"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)


"Jeff" wrote in message
...
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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Jeff" wrote:

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

"Ryan H" wrote:

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
--
Cheers,
Ryan


"Phil Hibbs" wrote:

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

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.

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
Rick, you might want to have a look at that again !

Regards,
Peter T

"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)


"Jeff" wrote in message
...
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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Jeff" wrote:

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

"Ryan H" wrote:

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
--
Cheers,
Ryan


"Phil Hibbs" wrote:

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

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

Regards,
Peter T

"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
Rick, you might want to have a look at that again !

Regards,
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


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)


"Jeff" wrote in message
...
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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Jeff" wrote:

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

"Ryan H" wrote:

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
--
Cheers,
Ryan


"Phil Hibbs" wrote:

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

Peter T wrote:
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.