excel - VBA Named Range dropping Sheet reference -

excel - VBA Named Range dropping Sheet reference -

i have named range called "myrange." it's define ='sheet1'!$a:$a. in vba, have formula lastrow= cells(rows.count, range("myrange").column).end(xlup).row. should homecoming lastly row info in named range. however, returns lastly row in column of whatever sheet on. why sheet reference beingness dropped named range , how remain in place?

try using this:

lastrow = range("myrange").worksheet.cells(rows.count, range("myrange").column).end(xlup).row

edit same method reply :) took long finish answer!

the reason failing cells references current active sheet default. adding range("myrange").worksheet before cells causes operate on worksheet "myrange" contained within.

excel vba excel-vba excel-2010 named-ranges