Macro to go to end of list

[Nobby]

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

 

[NateO]

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

Range("TypeofConcern").End(xlDown).Offset(1).Select

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-05-01 10:46

 

[Nobby]

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

 

[NateO]

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

 

[Nobby]

Sorry NatO - it does work.

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

Cheers

Nobby

 

[Nobby]

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

 

[NateO]

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

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

 

[NateO]

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

Yep, at the bottom of your code place this line:

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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-05-01 11:19

 

[Nobby]

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