How do I move the current cell to the top of the screen?

I have a spreadsheet price list with rows that contain headings in them, for
example, cables, displays, cabinets, etc. The spreadsheet has grown to be
several hundred rows long, and I wanted to have a way to easily get from one
place to another without having to scroll around all the time. I decided
that the best way to fix this would be to create macro buttons at the top few
rows of the screen that would take me to the particular sections in the
worksheet. So I researched how to create names for a particular cell, and
learned how to make a macro to goto that particular cell (using the name).
Everything works out pretty good so far. I have freeze panes set so that my
macro buttons stay at the top of the screen at all times.

The problem is that when I run the macros, it only takes me to that cell and
if the cell happens to be anywhere lower on the spreadsheet than what is
currently visible on the screen, it makes the cell appear at the bottom of
the screen. Since my intention is to see the contents of the rows just under
this row heading, I'd like to find a way to make the cell come up right under
my frozen panes. I've searched high and low for some command to bring that
cell to the "top" (with the exception of the frozen panes) of the screen.

Can anyone help me with this? It driving me nuts. Thanks.

Re-do your macros to FIRST take the cursor FAR to the right and below the
cell that you eventually want to wind up at (at least several screens full
of rows and columns), , THEN, set it to go to the cell you
want...........approaching it from that direction will place it at the
top.........

Vaya con Dios,
Chuck, CABGx3



"mad.cow" <[email protected]> wrote in message
news:[email protected]...
> I have a spreadsheet price list with rows that contain headings in them,
for
> example, cables, displays, cabinets, etc. The spreadsheet has grown to be
> several hundred rows long, and I wanted to have a way to easily get from
one
> place to another without having to scroll around all the time. I decided
> that the best way to fix this would be to create macro buttons at the top
few
> rows of the screen that would take me to the particular sections in the
> worksheet. So I researched how to create names for a particular cell, and
> learned how to make a macro to goto that particular cell (using the name).
> Everything works out pretty good so far. I have freeze panes set so that
my
> macro buttons stay at the top of the screen at all times.
>
> The problem is that when I run the macros, it only takes me to that cell
and
> if the cell happens to be anywhere lower on the spreadsheet than what is
> currently visible on the screen, it makes the cell appear at the bottom of
> the screen. Since my intention is to see the contents of the rows just
under
> this row heading, I'd like to find a way to make the cell come up right
under
> my frozen panes. I've searched high and low for some command to bring
that
> cell to the "top" (with the exception of the frozen panes) of the screen.
>
> Can anyone help me with this? It driving me nuts. Thanks.

Hi!

I have a similar setup in one of my files.

I'm not very good with VBA code but I used a line like this to fine tune
where the active window ends up:

ActiveWindow.SmallScroll ToLeft:=2

Depending on what direction you want to move, replace ToLeft with either
ToRight, Up or Down. It sounds like you want to go Up.

You'll have to experiment with the =?? value until you get it exactly where
you want it.

Biff

"mad.cow" <[email protected]> wrote in message
news:[email protected]...
>I have a spreadsheet price list with rows that contain headings in them,
>for
> example, cables, displays, cabinets, etc. The spreadsheet has grown to be
> several hundred rows long, and I wanted to have a way to easily get from
> one
> place to another without having to scroll around all the time. I decided
> that the best way to fix this would be to create macro buttons at the top
> few
> rows of the screen that would take me to the particular sections in the
> worksheet. So I researched how to create names for a particular cell, and
> learned how to make a macro to goto that particular cell (using the name).
> Everything works out pretty good so far. I have freeze panes set so that
> my
> macro buttons stay at the top of the screen at all times.
>
> The problem is that when I run the macros, it only takes me to that cell
> and
> if the cell happens to be anywhere lower on the spreadsheet than what is
> currently visible on the screen, it makes the cell appear at the bottom of
> the screen. Since my intention is to see the contents of the rows just
> under
> this row heading, I'd like to find a way to make the cell come up right
> under
> my frozen panes. I've searched high and low for some command to bring
> that
> cell to the "top" (with the exception of the frozen panes) of the screen.
>
> Can anyone help me with this? It driving me nuts. Thanks.

Thanks for the quick reply. I wasn't sure what you meant, so I tried a few
things. I scrolled way over to the right and way down and I created a name
for that cell as end_of_worksheet. I then redid the macros and went to the
end of the worksheet first and then went to the name of the row that I
wanted. This worked perfectly. Thank you very much for your quick reply.

"CLR" wrote:

> Re-do your macros to FIRST take the cursor FAR to the right and below the
> cell that you eventually want to wind up at (at least several screens full
> of rows and columns), , THEN, set it to go to the cell you
> want...........approaching it from that direction will place it at the
> top.........
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "mad.cow" <[email protected]> wrote in message
> news:[email protected]...
> > I have a spreadsheet price list with rows that contain headings in them,
> for
> > example, cables, displays, cabinets, etc. The spreadsheet has grown to be
> > several hundred rows long, and I wanted to have a way to easily get from
> one
> > place to another without having to scroll around all the time. I decided
> > that the best way to fix this would be to create macro buttons at the top
> few
> > rows of the screen that would take me to the particular sections in the
> > worksheet. So I researched how to create names for a particular cell, and
> > learned how to make a macro to goto that particular cell (using the name).
> > Everything works out pretty good so far. I have freeze panes set so that
> my
> > macro buttons stay at the top of the screen at all times.
> >
> > The problem is that when I run the macros, it only takes me to that cell
> and
> > if the cell happens to be anywhere lower on the spreadsheet than what is
> > currently visible on the screen, it makes the cell appear at the bottom of
> > the screen. Since my intention is to see the contents of the rows just
> under
> > this row heading, I'd like to find a way to make the cell come up right
> under
> > my frozen panes. I've searched high and low for some command to bring
> that
> > cell to the "top" (with the exception of the frozen panes) of the screen.
> >
> > Can anyone help me with this? It driving me nuts. Thanks.
>
>
>

Thanks for the message. CLR also responded and I was a bit confused at
first. However, I was able to try a few things and re-read the message and I
did exactly what it said. I had the macro first go to somewhere far away in
the spreadsheet, basically I went the far lower right part of the
spreadsheet, row 1000 and column IV. I created a name for that cell as
"end_of_worksheet". Then in my macros, I first went to the end of the
worksheet, and then to the cell that I wanted and it worked perfectly. The
whole spreadsheet blinks for a fraction of a second, but then it goes right
where I wanted it to go.

The reason it would be difficult for me to use a VB command to shift up or
down a specific number of cells is because this thing is constantly growing.
It's also the reason that I created names for the sections. That way, if I
have to add rows, I can and it won't destroy my macros.

"Biff" wrote:

> Hi!
>
> I have a similar setup in one of my files.
>
> I'm not very good with VBA code but I used a line like this to fine tune
> where the active window ends up:
>
> ActiveWindow.SmallScroll ToLeft:=2
>
> Depending on what direction you want to move, replace ToLeft with either
> ToRight, Up or Down. It sounds like you want to go Up.
>
> You'll have to experiment with the =?? value until you get it exactly where
> you want it.
>
> Biff
>
> "mad.cow" <[email protected]> wrote in message
> news:[email protected]...
> >I have a spreadsheet price list with rows that contain headings in them,
> >for
> > example, cables, displays, cabinets, etc. The spreadsheet has grown to be
> > several hundred rows long, and I wanted to have a way to easily get from
> > one
> > place to another without having to scroll around all the time. I decided
> > that the best way to fix this would be to create macro buttons at the top
> > few
> > rows of the screen that would take me to the particular sections in the
> > worksheet. So I researched how to create names for a particular cell, and
> > learned how to make a macro to goto that particular cell (using the name).
> > Everything works out pretty good so far. I have freeze panes set so that
> > my
> > macro buttons stay at the top of the screen at all times.
> >
> > The problem is that when I run the macros, it only takes me to that cell
> > and
> > if the cell happens to be anywhere lower on the spreadsheet than what is
> > currently visible on the screen, it makes the cell appear at the bottom of
> > the screen. Since my intention is to see the contents of the rows just
> > under
> > this row heading, I'd like to find a way to make the cell come up right
> > under
> > my frozen panes. I've searched high and low for some command to bring
> > that
> > cell to the "top" (with the exception of the frozen panes) of the screen.
> >
> > Can anyone help me with this? It driving me nuts. Thanks.
>
>
>

You're welcome..........sorry for the confusion..........

Vaya con Dios,
Chuck, CABGx3


"mad.cow" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the quick reply. I wasn't sure what you meant, so I tried a
few
> things. I scrolled way over to the right and way down and I created a
name
> for that cell as end_of_worksheet. I then redid the macros and went to
the
> end of the worksheet first and then went to the name of the row that I
> wanted. This worked perfectly. Thank you very much for your quick reply.
>
> "CLR" wrote:
>
> > Re-do your macros to FIRST take the cursor FAR to the right and below
the
> > cell that you eventually want to wind up at (at least several screens
full
> > of rows and columns), , THEN, set it to go to the cell you
> > want...........approaching it from that direction will place it at the
> > top.........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "mad.cow" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a spreadsheet price list with rows that contain headings in
them,
> > for
> > > example, cables, displays, cabinets, etc. The spreadsheet has grown
to be
> > > several hundred rows long, and I wanted to have a way to easily get
from
> > one
> > > place to another without having to scroll around all the time. I
decided
> > > that the best way to fix this would be to create macro buttons at the
top
> > few
> > > rows of the screen that would take me to the particular sections in
the
> > > worksheet. So I researched how to create names for a particular cell,
and
> > > learned how to make a macro to goto that particular cell (using the
name).
> > > Everything works out pretty good so far. I have freeze panes set so
that
> > my
> > > macro buttons stay at the top of the screen at all times.
> > >
> > > The problem is that when I run the macros, it only takes me to that
cell
> > and
> > > if the cell happens to be anywhere lower on the spreadsheet than what
is
> > > currently visible on the screen, it makes the cell appear at the
bottom of
> > > the screen. Since my intention is to see the contents of the rows
just
> > under
> > > this row heading, I'd like to find a way to make the cell come up
right
> > under
> > > my frozen panes. I've searched high and low for some command to bring
> > that
> > > cell to the "top" (with the exception of the frozen panes) of the
screen.
> > >
> > > Can anyone help me with this? It driving me nuts. Thanks.
> >
> >
> >

Mad Cow
Hi,
To stop the "whole spreadsheet blinks for a fraction of a second"
Put this at the start of your macro

Application.ScreenUpdating = False
'Turn screen updating off.

Then put this at the end of your macro

Application.ScreenUpdating = True
'Turn screen updating back on.

Thx
Dave

Originally Posted by mad.cow

Thanks for the message. CLR also responded and I was a bit confused at
first. However, I was able to try a few things and re-read the message and I
did exactly what it said. I had the macro first go to somewhere far away in
the spreadsheet, basically I went the far lower right part of the
spreadsheet, row 1000 and column IV. I created a name for that cell as
"end_of_worksheet". Then in my macros, I first went to the end of the
worksheet, and then to the cell that I wanted and it worked perfectly. The
whole spreadsheet blinks for a fraction of a second, but then it goes right
where I wanted it to go.

The reason it would be difficult for me to use a VB command to shift up or
down a specific number of cells is because this thing is constantly growing.
It's also the reason that I created names for the sections. That way, if I
have to add rows, I can and it won't destroy my macros.

"Biff" wrote:

> Hi!
>
> I have a similar setup in one of my files.
>
> I'm not very good with VBA code but I used a line like this to fine tune
> where the active window ends up:
>
> ActiveWindow.SmallScroll ToLeft:=2
>
> Depending on what direction you want to move, replace ToLeft with either
> ToRight, Up or Down. It sounds like you want to go Up.
>
> You'll have to experiment with the =?? value until you get it exactly where
> you want it.
>
> Biff
>
> "mad.cow" <[email protected]> wrote in message
> news:[email protected]...
> >I have a spreadsheet price list with rows that contain headings in them,
> >for
> > example, cables, displays, cabinets, etc. The spreadsheet has grown to be
> > several hundred rows long, and I wanted to have a way to easily get from
> > one
> > place to another without having to scroll around all the time. I decided
> > that the best way to fix this would be to create macro buttons at the top
> > few
> > rows of the screen that would take me to the particular sections in the
> > worksheet. So I researched how to create names for a particular cell, and
> > learned how to make a macro to goto that particular cell (using the name).
> > Everything works out pretty good so far. I have freeze panes set so that
> > my
> > macro buttons stay at the top of the screen at all times.
> >
> > The problem is that when I run the macros, it only takes me to that cell
> > and
> > if the cell happens to be anywhere lower on the spreadsheet than what is
> > currently visible on the screen, it makes the cell appear at the bottom of
> > the screen. Since my intention is to see the contents of the rows just
> > under
> > this row heading, I'd like to find a way to make the cell come up right
> > under
> > my frozen panes. I've searched high and low for some command to bring
> > that
> > cell to the "top" (with the exception of the frozen panes) of the screen.
> >
> > Can anyone help me with this? It driving me nuts. Thanks.
>
>
>