Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Macro to go to end of list

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need a macro to allow a user to go to the end of a list of data.

    The list is in a named range "TypeofConcern"

    So far I've got:

    Application.Goto Reference:="TypeofConcern"

    Set LastCell = [AA65536].End(xlUp)
    SelectCell
    End Sub

    This just selects the entire named range - and I want the user to be taken to the first blank cell at the end of the list.

    Anyone tell me what's missing?

    Cheers.

    Nobby

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If there are no empty cells in your range, try:

    Code:
    Range("TypeofConcern").End(xlDown).Offset(1).Select
    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-01 10:46 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cheers NatO, that will work, except that it selects the last cell with data.

    What do I need to add to make it jump down 1 cell?

    Nobby

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Re worked it, see above. Sorry about unannounced edit.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry NatO - it does work.

    I hadn't picked up the Offset(1) part when I c+p

    Cheers

    Nobby

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    NatO,

    All working OK, but from a user point of view, it would be better if, once the blank cell is found, it was centered in the middle of the screen rather than appearing at the edge.

    Is there a simple way of telling the macro to centre the view?

    Nobby

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Scratch that, use the following, you won't get errors if you're on a different worksheet while executing the code:

    Code:
    Application.Goto Range("TypeofConcern").End(xlDown).Offset(1)

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there a simple way of telling the macro to centre the view
    Yep, at the bottom of your code place this line:

    Code:
    CenterOnCell ActiveCell
    Now go to the following site:

    http://www.cpearson.com/excel/zoom.htm

    And place Chip's procedure "CenterOnCell(OnCell As Range)" in the same code module.

    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-01 11:19 ]

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    NatO,

    Added it all in, together with Chip's code and all's working well.

    A good nights work! Thanks for the help.

    Cheers.

    Nobby

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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