Jump to first blank row in excel file on open?????

[sandydoull]

Is there a simple way to have an excel file jump to the last row in a worksheet on open?

(Just to save scrolling all the way down every time i open the file)



Sandy Doull

http://www.sandydoull.com

 

[carrr]

Using Ctrl+the down arrow will take you to the last filled cell/row

Tired of waiting for an answer? Try asking better questions. See: faq222-2244

 

[SilentAiche]

Sandydoull,

I use old Lotus keystrokes to navigate, just out of habit.

[blue]End+Home[/blue] - goes to lowest right (used) cell of the sheet

[blue]End+(any)Arrow[/blue] - goes to next cell in the chosen direction that is null/not null, which ever condition is opposite from the original active cell. For example, if B2 is your active cell and it is empty when you press End+Down Arrow, it will move the next cell that is NOT empty. Works the same for up, down, left & right. Play with it and quickly see. I believe you can substitute Control for End and get the same results (if you're not an old 1-2-3er).

Having said all that, I get the feeling you wanted something automated. Try recording a macro using the keystrokes just described (perhaps End+Home, Home, Down Arrow) to get to the lowest empty cell in Column A. Then set this to run On Open. But be careful - this will take you to the lowest cell in column A for a row that has not been used. If you have a bunch of deleted rows, you could wind up well below your intended target. To account for this, add two more steps to the macro (End+Up Arrow, Down Arrow). The problem with this one is that it takes you to the lowest empty cell in Column A; however, that doesn't mean that that ROW isn't being used in other columns, perhaps for totals.

[purple]Note: I am not completely sure how to make this operate automatically on opening the file; however, if the concept has any merit, someone on this forum can help you- and I'll learn something too![/purple]

You know, the more I write the more I think you should just skip the macro- open the file, hit End+Home and Home, and then see where you are...

Good Luck! (and hope this helps a little)
Tim


Good luck,
Tim

 

[AnotherHiggins]

SilentAiche,

Just an FYI:
[Ctrl]+[End] goes to the end of the file (one less keystroke than what you're using)
[Ctrl]+[Home] goes to the top (with an exception if you have frames)
[Ctrl]+[Arrow] works as you described [End]+[Arrow] working.

If a macro is desired, then you could place something like this in the Workbook section of the VBE:

Private Sub Workbook_Open()
Range("A65536").End(xlUp).Offset(1).Select
End Sub

But I hate having to enable the macro every time the file is opened.

sandydoull,
I'd either go with Carr's suggestion of [Ctrl]+[Down Arrow] or [Ctrl]+[End].

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[SilentAiche]

anotherhiggins,

Many, many thanks for helping Sandy (I knew, just knew, someone at Tek-Tips would). I tried, but may have gone beyond my ability.

Out of curiosity (I would test, but I'm not at work), does Ctl+Arrow skip null/non-null cells?

To Carrr: please understand I wasn't trying to undercut your solution; I really thought I had a different interpretation of the question (w.r.t., automating the process).

Best wishes, and many thanks, to all!

Tim

 

[sandydoull]

Thanks everyone for the ideas!


Sandy Doull

http://www.sandydoull.com

 

[carrr]

SilentAiche,

Not at all. These forums are for everyone to participate in. I'm glad you jumped in the fray.

Best to you.

Tired of waiting for an answer? Try asking better questions. See: faq222-2244

 

[AnotherHiggins]

SilentAiche,

[Ctrl]+[Arrow] pretty much works just like [End]+[Arrow] (as you've probably discovered by now). AFAIK, the only advantages it has over [End]+[Arrow] is that: 1) you don't have to keep pressing [Ctrl] every time as you'd have to press [End] repeatedly if you want to jump through multiple sections; and 2) you can use the left [Ctrl] which - for me at least - allows my hands to stay closer to 'home position'.

I've said it before and I'll say it again: I'm a lazy, lazy man. I love shortcuts, and I particulary love the shortest/fastest shortcuts. But if you're comfortable using the [End] method there's no reason to change.

And there's no reason to appologize for offering different ideas about how to work around a problem. We all learn from each other here, and - as far as I'm concerned - your participation in this forum is appreciated!



[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886