Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2006

Tip: Looking for answers? Try searching our database.

Why doesn't 'Range("A65000").Select' work?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Alan - 22 Jan 2006 23:28 GMT
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
Juan Pablo González - 22 Jan 2006 23:33 GMT
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.

Signature

Regards,

Juan Pablo González
Excel MVP

> Hello world:
>
[quoted text clipped - 25 lines]
>
> Alan
Mark Lincoln - 22 Jan 2006 23:35 GMT
Are you trying to select A65000 on one sheet using a macro from another
sheet?  That's not permitted.
Mark Lincoln - 22 Jan 2006 23:38 GMT
I forgot to mention that if you put that code in a module, you can use
it from any sheet.
Dave Peterson - 22 Jan 2006 23:40 GMT
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.

> Hello world:
>
[quoted text clipped - 25 lines]
>
> Alan

Signature

Dave Peterson

Tom Ogilvy - 23 Jan 2006 00:04 GMT
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.

Signature

Regards,
Tom Ogilvy

> Hello world:
>
[quoted text clipped - 25 lines]
>
> Alan
Alan - 23 Jan 2006 00:19 GMT
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

> Hello world:
>
[quoted text clipped - 25 lines]
>
> Alan
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.