VBA Function for finding Excel maxrows

I am trying to figure out how to test the version number/type of a workbook and then return what the maximum number of rows that workbook can take using a function in VBA.  Does such a thing exist?
LVL 1
simonwaitAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
activesheet.rows.count

will give you the rows in the active sheet.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kyle AbrahamsSenior .Net DeveloperCommented:
http://www.mrexcel.com/forum/showthread.php?t=357733

dim MaxRows as int
if application.version >= 12.0 then
     'code goes here
MaxRows = 1048576
else
   maxRows = 65536
 end if

http://vbadud.blogspot.com/2010/02/maximum-limit-of-rows-columns-etc-in.html
0
simonwaitAuthor Commented:
Thanks, what I needed to check was the maximum number of rows I could use before the excel limits for that version kicked in (ie Excel 2010 = 1,048,576 lines but 97 is 16,384).  I figured out a way but it may not be accurate?  Any thoughts?
excelver = ActiveWorkbook.FileFormat
Select Case excelver
Case 50 Or 51 Or 52
excelmaxrows = 1048576
Case 56
excelmaxrows = 65536
Case Else
excelmaxrows = 16384
End Select

Open in new window

0
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

simonwaitAuthor Commented:
Thanks for that ged325 but what if I am looking at a 2003 xls on Excel 2010?  wouldnt that show as 1048576 rows were possible not 65536?
0
Saqib Husain, SyedEngineerCommented:
You do not need all that. Simply

Sheets("Sheet1").rows.count

will give the number of rows in that sheet.
0
andrewssd3Commented:
As Saqib says you can use ActiveSheet.Rows.Count, or you can use Application.Rows.Count, which will return the higher or lower number depending on the capabilities of the active workbook.  You should always use these properties rather than literals - who knows what the limit will be in the next version of Excel - some time it will go up to 2,147,483,648!
0
Kyle AbrahamsSenior .Net DeveloperCommented:
I forgot about the rows.count function . . . that's the most efficient and guaranteed to return the correct number every time . . . most likely the best way to go.
0
simonwaitAuthor Commented:
Ah sorry, thats the trouble with assumption!  I assumed that it was the same as activesheet.usedrange.rows.count.  Should have stopped and thought...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.