0

I have a named range called "MyRange." It's define as ='Sheet1'!$A:$A. In VBA, I have the formula LastRow= Cells(Rows.Count, Range("MyRange").Column).End(xlUp).Row. It should return the last row with data in the Named Range. However, it returns the last row in column A of whatever sheet I am currently on. Why is the sheet reference being dropped from the named range and how do I get it to stay in place?

user2242044
  • 8,803
  • 25
  • 97
  • 164

3 Answers3

2

The syntax of your code is such that for both the Cells argument, and the Rows argument, it will refer to the active sheet and not to the sheet in the named range.

Syntax to maintain the sheet reference could be:

With Range("myrange").Worksheet
    LastRow = .Cells(.Rows.Count, [myrange].Column).End(xlUp).Row
End With

Note the "dot" in front of Cells, and Rows (and Column).

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
1

Try using this:

LastRow = range("MyRange").Worksheet.Cells(Rows.Count, Range("MyRange").Column).End(xlUp).Row

EDIT Same method as another answer :) Took too long to finish the answer!

The reason it was failing is that CELLS references the current active sheet by default. Adding the Range("MyRange").Worksheet before the CELLS causes it to operate on the Worksheet that "MyRange" is contained within.

Ken
  • 1,156
  • 1
  • 6
  • 8
0

You can always try:

With Sheet1
    LastRow = Cells(Rows.Count, Range("MyRange").Column).End(xlUp).Row
End With

Another thought: try what you have without the "" around MyRange

LastRow= Cells(Rows.Count, Range(MyRange).Column).End(xlUp).Row
Tanner
  • 548
  • 8
  • 20
  • Your first suggestion works but is not a very scalable approach as I have several thousand references to named ranges. That would add about 5,000 lines of code. The second option does not work. – user2242044 Jun 18 '14 at 19:09
  • 1
    @tannman357, your answer is good , except you forgot some ponctuation: one before cells and one before rows. So corrected it reads : `LastRow = .Cells(.Rows.Count, .Range("MyRange").Column).End(xlUp).Row` , or else you going just in activesheet, again... Also, i guess its `with sheets("Sheet1")` – Patrick Lepelletier Jun 18 '14 at 19:28
  • 1
    @user2242044 - include things like _scalable_ in your OP. Otherwise its like trying to hit a moving target to give you a _good_ answer. – ryyker Jun 18 '14 at 19:51