Solved

VBA Function for finding Excel maxrows

Posted on 2012-03-29
8
316 Views
Last Modified: 2012-03-30
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?
0
Comment
Question by:simonwait
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 37784144
activesheet.rows.count

will give you the rows in the active sheet.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37784174
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
 
LVL 1

Author Comment

by:simonwait
ID: 37784183
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 1

Author Comment

by:simonwait
ID: 37784189
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37784198
You do not need all that. Simply

Sheets("Sheet1").rows.count

will give the number of rows in that sheet.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 37784317
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37784390
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
 
LVL 1

Author Comment

by:simonwait
ID: 37785997
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

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question