+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Alan
    Guest

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

    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



  2. #2
    Juan Pablo González
    Guest

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

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  3. #3
    Mark Lincoln
    Guest

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

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


  4. #4
    Mark Lincoln
    Guest

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

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


  5. #5
    Dave Peterson
    Guest

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

    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

  6. #6
    Tom Ogilvy
    Guest

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

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >
    >




  7. #7
    Alan
    Guest

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

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1