• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count number of Rows in other workbook

sweetness

New Member
Hi all,

I'm trying to count the number of rows in a table in another workbook that I can reference later for a loop.

Code:
Sub Count ()
Const book2Name = "Controls.xlsx"
Dim book2 As Workbook
Set book2 = Workbooks(book2Name)
numRows = book2.Sheets("Sheet1").Range("Table1").Rows.Count
End Sub

Thank you in advance
 
Hi,

The below code will fetch you the last active cell/row number in column A

numRows = Range("A1048576").End(xlUp).Row
 
Hi Sathish,

Thank you for your response however that does not solve my problem it is because I am trying to count a sheet that is in an entirely different workbook. Any idea's?
 
Hi sweetness,

Your OP's macro runs fine on my machine, assuming other workbook is open. Is it not giving you the desired result, or crashing?
 
Is the 2nd workbook open?
Is there really a range named "Table1"? Or is it an actual XL table (which is a ListObject)
Code:
'How to find rows in range
x = Range("MyTable").Rows.Count
'How to find rows in table
x = ListObjects("MyTable").Range.Rows.Count
 
Hey Luke,

Yes the 2nd workbook is open
No you are correct it is a table not a range. I changed the code with your suggestion but it still does not work
Code:
numRows = book2.Sheets("Sheet1").ListObjects("MyTable").Range.Rows.Count
book2 is the workbook
Sheet1 is the name of the sheet where the data is located in book2
MyTable is the table where the data is located within the sheet

Thanks
 
Hi ,

Your original code should run fine ; it does for me. Going by your latest post , you need to change it as follows :
Code:
Sub Count ()
    Const book2Name = "Controls.xlsx"
    Dim book2 As Workbook
    Set book2 = Workbooks(book2Name)
    numRows = book2.Sheets("Sheet1").Range("MyTable").Rows.Count
End Sub
Narayan
 
Hi Narayank991,

I have tried that and it still says subscript out of range and has highlighted the following:
Code:
 numRows = book2.Sheets("Sheet1").Range("MyTable").Rows.Count

Thanks
 
Hi ,

Like I mentioned earlier , the code I posted works perfectly at my end.

When you get the error , can you click on the Debug button , and type in the following in the Immediate window :

?book2.Name

?Activesheet.Name

?book2.Sheets("Sheet1").Range("MyTable").address

and post the results of what is displayed against each statement ?

Narayan
 
Hey Luke,

Yes the 2nd workbook is open
No you are correct it is a table not a range. I changed the code with your suggestion but it still does not work
Code:
numRows = book2.Sheets("Sheet1").ListObjects("MyTable").Range.Rows.Count
book2 is the workbook
Sheet1 is the name of the sheet where the data is located in book2
MyTable is the table where the data is located within the sheet

Thanks
Is "MyTable" the actual name of your table? If you select the Table, then go to Table Tools ribbon, name should appear on far left. You will need to specify the exact name.
 
Back
Top