Last Row

[michaelm702]

I have a macro which opens a new excel. Now I want to get the count of used rows in the excel which is opened. I tried this
row = ActiveSheet.UsedRange.Rows.Count
but it returns the count of the rows from the excel which the macro is being run.

[Simon Lloyd]

If your opening another workbook via Workbook.open then after that statement you should perhaps put

Sub countit()
Dim Row
With ActiveWorkbook
Row = ActiveSheet.UsedRange.Rows.Count
MsgBox "There have been " & Row & " Used"
End With
End Sub
but you may even have to state the sheet like

With ActiveWorkBook.Sheets("Sheet1")
Regards,
Simon

[Bob Phillips]

When you say you open a new Excel do you really mean that, or do you mean you open a new workbook.

If the former, then you need to qualify everything with the Excel object and the workbook and the sheet.

If the latter, after opening, just get the count from the activesheet.

But far better to set object variables to keep track of the various objects and reference visa these.

[michaelm702]

I am using
Workbooks.Open Filename:="test.xls"
now after this i want the count of rows in the test.xls.

[michaelm702]

Simon the code you have given does not work as it returns the count of the excel which the macro is being run from and not the newly opened excel

[Simon Lloyd]

This definately works!Sub countit()
Dim Row
Workbooks.Open ("test.xls")
With Workbooks("test.xls")
Row = ActiveSheet.UsedRange.Rows.Count
MsgBox "There have been " & Row & " Used"
End With
End SubRegards,
Simon

[Ken Puls]

Hi there,

Personally, I prefer in this case to set the new workbook to a variable so that I know exactly who I'm dealing with. I also avoid the "activesheet" if I can:

Sub countit()
Dim wb As Workbook
Set wb = Workbooks.Open("test.xls")
With wb.Worksheets(1)
MsgBox "There have been " & .UsedRange.Rows.Count & " Used"
End With
Set wb = Nothing
End Sub

:)