How to find the number of rows in Excel

[lmclaus]

I have a spreadsheet which I am parsing with VBA to create another spreadsheet with the information in another format.
How do I know how many rows there are in the source spreadsheet and how do I know when I have finished reading all of the rows?

 

[VulcanJedi]

Sub dd()

Application.ActiveSheet.UsedRange.Select

End Sub
Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates others navigating through the threads / posts!

 

[xlbo]

Check the FAQs
faq707-2115 Rgds
~Geoff~

 

[lmclaus]

I may not know how to use the information you gave me but let me try to give a bit more information on what I want to do. I have an existing Excel spreadsheet that I want to extract certain cells from each row to create a new spreadsheet. For example. I want to place A1,A4,A7 in spreadsheet one to A1,A2,A3 in spreadsheet two. It would seem to me that I need to do something like a While (I am not at the last row + 1) do the write to the other spreadsheet. Do I need to select the whole spreadsheet before I do the While statement? I am using Excel 2000 on Windows 2000. Thanks.

 

[RobBroekhuis]

If your table does not include empty rows, you could simply do something like

if activecell<>&quot;&quot; then ...

if you do have empty rows, you can use

LastRow=activesheet.usedrange.range(&quot;A1&quot.row+activesheet.usedrange.rows.count-1

and then later, inside your loop:

if activecell.row<=LastRow then ...

Make sense?
Rob

 

[lmclaus]

This indeed makes sense and works perfectly. Thanks for all of the help. This is really a great resource.