Hello world:

I'm using Excel 2003. I'm baffled.
Something that has worked for me since Excel 5 no longer works.
Maybe someone out there can explain what I'm doing wrong.

When I go to a sheet, I often have code like this to tell me the last data
row:
'--Find last data row
Range("A65000").Select
Selection.End(xlUp).Select
lngLastDataRow = ActiveCell.Row

That has worked fine for years, and it works fine for other sheets in this
workbook.
On one sheet, however, I get the following message when it gets to the first
command:
Error 1004
Select Method of Range Class Failed

I have checked the sheet's Protection, and it is all off.
I have created a new workbook with just the two sheets necessary for this
code, and still the error.

What have I done to create this error?

Help!

Alan

Using Select fails sometimes for no apparent or reasonable reason. In your
case, you can do without it

lngLastDataRow = Cells(Rows.Count, 1).End(xlUp).Row

all in one go, no selection.

--
Regards,

Juan Pablo González
Excel MVP

"Alan" wrote in message
...
Hello world:

I'm using Excel 2003. I'm baffled.
Something that has worked for me since Excel 5 no longer works.
Maybe someone out there can explain what I'm doing wrong.

When I go to a sheet, I often have code like this to tell me the last data
row:
'--Find last data row
Range("A65000").Select
Selection.End(xlUp).Select
lngLastDataRow = ActiveCell.Row

That has worked fine for years, and it works fine for other sheets in this
workbook.
On one sheet, however, I get the following message when it gets to the
first command:
Error 1004
Select Method of Range Class Failed

I have checked the sheet's Protection, and it is all off.
I have created a new workbook with just the two sheets necessary for this
code, and still the error.

What have I done to create this error?

Help!

Alan

Are you trying to select A65000 on one sheet using a macro from another
sheet? That's not permitted.

I forgot to mention that if you put that code in a module, you can use
it from any sheet.

If this code is behind a worksheet, then that unqualified range refers to the
worksheet holding the code--not any other sheet that you may have selected.



Alan wrote:

Hello world:

I'm using Excel 2003. I'm baffled.
Something that has worked for me since Excel 5 no longer works.
Maybe someone out there can explain what I'm doing wrong.

When I go to a sheet, I often have code like this to tell me the last data
row:
'--Find last data row
Range("A65000").Select
Selection.End(xlUp).Select
lngLastDataRow = ActiveCell.Row

That has worked fine for years, and it works fine for other sheets in this
workbook.
On one sheet, however, I get the following message when it gets to the first
command:
Error 1004
Select Method of Range Class Failed

I have checked the sheet's Protection, and it is all off.
I have created a new workbook with just the two sheets necessary for this
code, and still the error.

What have I done to create this error?

Help!

Alan

--

Dave Peterson

Just to summarize because all answers have not been consistent:

Assuming that as others have surmised, the code is in a sheet module for a
sheet that is not the active sheet. For discussion purposes assume a
Commandbutton1 on Sheet1 has been clicked.

Private Sub CommandButton1_Click()
Dim lngLastDataRow as Long
With Worksheets("Sheet2")
.Activate
.Range("A65000").Select
Selection.End(xlUp).Select
lngLastDataRow = ActiveCell.Row
End with
' do something with the results
End sub

As Dave said, an unqualified Range reference implicitely refers to the sheet
containing the code. So the above qualifies the reference.

But better is as Juan suggested

Private Sub CommandButton1_Click()
Dim lngLastDataRow as Long
With Worksheets("Sheet2")
lngLastDataRow = _
.Cells(rows.count,1).End(xlup).Row
End with
' do something with the results
End sub

Of course either of these constructs would have to be repeated in the "do
something with the results" part of your code or you will encounter the same
problem.

--
Regards,
Tom Ogilvy


"Alan" wrote in message
...
Hello world:

I'm using Excel 2003. I'm baffled.
Something that has worked for me since Excel 5 no longer works.
Maybe someone out there can explain what I'm doing wrong.

When I go to a sheet, I often have code like this to tell me the last data
row:
'--Find last data row
Range("A65000").Select
Selection.End(xlUp).Select
lngLastDataRow = ActiveCell.Row

That has worked fine for years, and it works fine for other sheets in this
workbook.
On one sheet, however, I get the following message when it gets to the
first
command:
Error 1004
Select Method of Range Class Failed

I have checked the sheet's Protection, and it is all off.
I have created a new workbook with just the two sheets necessary for this
code, and still the error.

What have I done to create this error?

Help!

Alan

Hello world:

Yay! It works!

Many thanks to all of you for answering so quickly and correctly.
As many of you surmised, I was trying to run one sheet's code on another
sheet.
When I moved the code to my code module, and called it from there, it worked
fine.

Whew! That was driving me nuts (and I don't have far to go).

Thanks again.

Alan

"Alan" wrote in message
...
Hello world:

I'm using Excel 2003. I'm baffled.
Something that has worked for me since Excel 5 no longer works.
Maybe someone out there can explain what I'm doing wrong.

When I go to a sheet, I often have code like this to tell me the last data
row:
'--Find last data row
Range("A65000").Select
Selection.End(xlUp).Select
lngLastDataRow = ActiveCell.Row

That has worked fine for years, and it works fine for other sheets in this
workbook.
On one sheet, however, I get the following message when it gets to the
first command:
Error 1004
Select Method of Range Class Failed

I have checked the sheet's Protection, and it is all off.
I have created a new workbook with just the two sheets necessary for this
code, and still the error.

What have I done to create this error?

Help!

Alan