Last row in a particular sheet / workbook

[Michael R]

I have a UDF to return the last row in a particular sheet:

Function Get_LastRow(Sheet As String) As Long
Application.Volatile True

Get_LastRow = Sheets(Sheet).UsedRange.Rows.Count

End Function

Now here is the problem:
* This UDF is stored in a module in workbook WB1.
* WB1 Sheet1 has 10 rows. WB1 Sheet2 has 9 rows. I call the UDF from WB1
Sheet3 twice to return the last rows of WB1 Sheet1 (10) and WB1 Sheet2 (9).
* At the same time I have workbook WB2 open. WB2 Sheet1 has 6 rows, WB2
Sheet2 has 3 rows. WB2 uses some results from WB1. WB2 has the focus - it is
displayed on top.
* When I do a Full Recalculate (F9) with WB2 on top, then switch to WB1 I
find that the UDF has put 6 and 3 in WB1. I have tried renaming Sheet1 and
Sheet2 in WB2 to something else, then the recalculate results in #VALUE in
WB1.
(When I recalculate with WB1 on top I receive the expected results 10 and 9.)

How can I get the last row of the sheet from the workbook that calls the UDF?

Thanks for your help!

 

[Joel]

I assume from you example the the Sheet is the SheetName. You need to pas
something like this

"[book1.xls]Sheet1"

You could do this
set WB1 = workbooks("Book1.xls")
LastRow = Get_LastRow("[" & WB1.name & "]" & "Sheet1")


or

set WB1 = workbooks("Book1.xls")
set sht = WB1.sheets("Sheet1")
LastRow = Get_LastRow("[" sht.parent.name & "]" & sht.name)

 

[Michael R]

Joel,

Thanks for your suggestions.

2 points though:
1) The UDF is called directly from a cell on WB1 Sheet3, Thus I suspect that
we would need to determine the workbook name out there. =Cell("filename")
does not work because it returns the name of the active workbook which in my
example is WB2.
2) I rather not hardcode the workbook names

I assume from you example the the Sheet is the SheetName. You need to pas
something like this

"[book1.xls]Sheet1"

You could do this
set WB1 = workbooks("Book1.xls")
LastRow = Get_LastRow("[" & WB1.name & "]" & "Sheet1")


or

set WB1 = workbooks("Book1.xls")
set sht = WB1.sheets("Sheet1")
LastRow = Get_LastRow("[" sht.parent.name & "]" & sht.name)

I have a UDF to return the last row in a particular sheet:

Function Get_LastRow(Sheet As String) As Long
Application.Volatile True

Get_LastRow = Sheets(Sheet).UsedRange.Rows.Count

End Function

Now here is the problem:
* This UDF is stored in a module in workbook WB1.
* WB1 Sheet1 has 10 rows. WB1 Sheet2 has 9 rows. I call the UDF from WB1
Sheet3 twice to return the last rows of WB1 Sheet1 (10) and WB1 Sheet2 (9).
* At the same time I have workbook WB2 open. WB2 Sheet1 has 6 rows, WB2
Sheet2 has 3 rows. WB2 uses some results from WB1. WB2 has the focus - it is
displayed on top.
* When I do a Full Recalculate (F9) with WB2 on top, then switch to WB1 I
find that the UDF has put 6 and 3 in WB1. I have tried renaming Sheet1 and
Sheet2 in WB2 to something else, then the recalculate results in #VALUE in
WB1.
(When I recalculate with WB1 on top I receive the expected results 10 and 9.)

How can I get the last row of the sheet from the workbook that calls the UDF?

Thanks for your help!

 

[Joel]

In VBA help see : Caller Property


If you are passing a parameter as a range object then you have to the infor
you need


function Myfunction(target as range)
MyAddress = target.address(external:=true)

end function

Or the parent of the range is the sheet and the sheet parent is the workbook

set sht = target.parent
shtName = sht.name
set bk = sht.name
bkname = bk.name


end function

Joel,

Thanks for your suggestions.

2 points though:
1) The UDF is called directly from a cell on WB1 Sheet3, Thus I suspect that
we would need to determine the workbook name out there. =Cell("filename")
does not work because it returns the name of the active workbook which in my
example is WB2.
2) I rather not hardcode the workbook names

I assume from you example the the Sheet is the SheetName. You need to pas
something like this

"[book1.xls]Sheet1"

You could do this
set WB1 = workbooks("Book1.xls")
LastRow = Get_LastRow("[" & WB1.name & "]" & "Sheet1")


or

set WB1 = workbooks("Book1.xls")
set sht = WB1.sheets("Sheet1")
LastRow = Get_LastRow("[" sht.parent.name & "]" & sht.name)

I have a UDF to return the last row in a particular sheet:

Function Get_LastRow(Sheet As String) As Long
Application.Volatile True

Get_LastRow = Sheets(Sheet).UsedRange.Rows.Count

End Function

Now here is the problem:
* This UDF is stored in a module in workbook WB1.
* WB1 Sheet1 has 10 rows. WB1 Sheet2 has 9 rows. I call the UDF from WB1
Sheet3 twice to return the last rows of WB1 Sheet1 (10) and WB1 Sheet2 (9).
* At the same time I have workbook WB2 open. WB2 Sheet1 has 6 rows, WB2
Sheet2 has 3 rows. WB2 uses some results from WB1. WB2 has the focus - it is
displayed on top.
* When I do a Full Recalculate (F9) with WB2 on top, then switch to WB1 I
find that the UDF has put 6 and 3 in WB1. I have tried renaming Sheet1 and
Sheet2 in WB2 to something else, then the recalculate results in #VALUE in
WB1.
(When I recalculate with WB1 on top I receive the expected results 10 and 9.)

How can I get the last row of the sheet from the workbook that calls the UDF?

Thanks for your help!

 

[Michael R]

Thanks a lot, Joel,

That works perfectly.

In VBA help see : Caller Property


If you are passing a parameter as a range object then you have to the infor
you need


function Myfunction(target as range)
MyAddress = target.address(external:=true)

end function

Or the parent of the range is the sheet and the sheet parent is the workbook

set sht = target.parent
shtName = sht.name
set bk = sht.name
bkname = bk.name


end function

Joel,

Thanks for your suggestions.

2 points though:
1) The UDF is called directly from a cell on WB1 Sheet3, Thus I suspect that
we would need to determine the workbook name out there. =Cell("filename")
does not work because it returns the name of the active workbook which in my
example is WB2.
2) I rather not hardcode the workbook names

I assume from you example the the Sheet is the SheetName. You need to pas
something like this

"[book1.xls]Sheet1"

You could do this
set WB1 = workbooks("Book1.xls")
LastRow = Get_LastRow("[" & WB1.name & "]" & "Sheet1")


or

set WB1 = workbooks("Book1.xls")
set sht = WB1.sheets("Sheet1")
LastRow = Get_LastRow("[" sht.parent.name & "]" & sht.name)

:

I have a UDF to return the last row in a particular sheet:

Function Get_LastRow(Sheet As String) As Long
Application.Volatile True

Get_LastRow = Sheets(Sheet).UsedRange.Rows.Count

End Function

Now here is the problem:
* This UDF is stored in a module in workbook WB1.
* WB1 Sheet1 has 10 rows. WB1 Sheet2 has 9 rows. I call the UDF from WB1
Sheet3 twice to return the last rows of WB1 Sheet1 (10) and WB1 Sheet2 (9).
* At the same time I have workbook WB2 open. WB2 Sheet1 has 6 rows, WB2
Sheet2 has 3 rows. WB2 uses some results from WB1. WB2 has the focus - it is
displayed on top.
* When I do a Full Recalculate (F9) with WB2 on top, then switch to WB1 I
find that the UDF has put 6 and 3 in WB1. I have tried renaming Sheet1 and
Sheet2 in WB2 to something else, then the recalculate results in #VALUE in
WB1.
(When I recalculate with WB1 on top I receive the expected results 10 and 9.)

How can I get the last row of the sheet from the workbook that calls the UDF?

Thanks for your help!