Using VB6, how to get a row count of an Excel spreadsheet

[kw]

I need to determine the number of rows in an Excel spreadsheet that are in the file my VB6 program is reading.&nbsp;&nbsp;Is there some equivalent 'count' function that applies here?&nbsp;&nbsp;Or do I need to loop through the entire file and count the rows manually??<br><br>Thanks for the help.<br>Kathy

 

[dsi]

I do not know of any 'count' function. That, of course, does not guarantee there isn't one. I manually cycle through the cells of a row that always contains data to count the rows.<br><br>Workbooks.Open FileName:=&quot;C:\FileName.XLS&quot;, ReadOnly:=True<br>Workbooks.Application.Visible = False<br>i = 1<br>Do While Len(Range(&quot;A&quot; & i).Text) &gt; 0<br>&nbsp;&nbsp;&nbsp;&nbsp;i = i + 1<br>Loop<br>'i is now the row number of the next available cell<br><br>If you find a count function, please let me know by posting it here. Thanks!

 

[rekclaw]

I agree with tclere.&nbsp;&nbsp;This is the same basic technique that I normally use (except I use cell offsets).&nbsp;&nbsp;<br><br>There are routines in Excel to jump to the last row of the spreadsheet, but they were not too reliable in early versions.&nbsp;&nbsp;Try this test though.&nbsp;&nbsp;Create a bunch of rows in your spreadsheet (make sure you include some empty row before the last row).&nbsp;&nbsp;Select the cell in the first row.&nbsp;&nbsp;Turn on your macro recorder then press Control-End.&nbsp;&nbsp;This should take you to the last row in the spreadsheet.&nbsp;&nbsp;Turn off your macro recorder and look at the source code that was generated for you.&nbsp;&nbsp;I believe the activecell object has a row property to tell you which row you are on thus giving you the row number.&nbsp;&nbsp;These two chunks of code should be able to get your row count.&nbsp;&nbsp;<br><br>As I said, I found that with early versions of Excel, tclere's technique was more reliable.<br><br>Good luck