Home or Ctrl-Home

Please tell me the VBA code for these keystrokes
Thanks

Home =
Cells(ActiveCell.Row,"A").Select

Ctrl+Home=
Range("A1").Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"noyb" <[email protected]> wrote in message
news:[email protected]...
> Please tell me the VBA code for these keystrokes
> Thanks

Hi Noyb,

Try:

ActiveCell.EntireRow.Cells(1).Select

Cells(1).Select

However, using VBA, it is rarely necessary to make selections and it is
frequently inefficient to do so.

Noramally, a better approach would be to set the required cell to a range
variable and manipulate the variable, e.g:

Dim Rng as Range

Set Rng = ActiveCell.EntireRow.Cells(1)
Rng.Interior.ColorIndex = 6

---
Regards,
Norman



"noyb" <[email protected]> wrote in message
news:[email protected]...
> Please tell me the VBA code for these keystrokes
> Thanks

Thanks, but, let me restate. I have a number of worksheets I want to
cycle through and get the selected cell to the top left of the
non-frozen part of each sheet. The number of frozen rows and columns
differs from sheet to sheet. Is it possible to so this without having to
hard code the location for each sheet. ie: the vba equivalent of
"Ctrl+Home" with frozen panes.
Thanks again

Chip Pearson wrote:
> Home =
> Cells(ActiveCell.Row,"A").Select
>
> Ctrl+Home=
> Range("A1").Select
>
>

Actually don't want to select anything, just want to position cursor at
top of sheet, taking into consideration frozen panes.
Thanks

Norman Jones wrote:
> Hi Noyb,
>
> Try:
>
> ActiveCell.EntireRow.Cells(1).Select
>
> Cells(1).Select
>
> However, using VBA, it is rarely necessary to make selections and it is
> frequently inefficient to do so.
>
> Noramally, a better approach would be to set the required cell to a range
> variable and manipulate the variable, e.g:
>
> Dim Rng as Range
>
> Set Rng = ActiveCell.EntireRow.Cells(1)
> Rng.Interior.ColorIndex = 6
>
> ---
> Regards,
> Norman
>
>
>
> "noyb" <[email protected]> wrote in message
> news:[email protected]...
>
>>Please tell me the VBA code for these keystrokes
>>Thanks
>
>
>

It took me forever and a day to find this one...

Cells(ActiveWindow.ActivePane.ScrollRow,
ActiveWindow.ActivePane.ScrollColumn).Select



"noyb" wrote:

> Thanks, but, let me restate. I have a number of worksheets I want to
> cycle through and get the selected cell to the top left of the
> non-frozen part of each sheet. The number of frozen rows and columns
> differs from sheet to sheet. Is it possible to so this without having to
> hard code the location for each sheet. ie: the vba equivalent of
> "Ctrl+Home" with frozen panes.
> Thanks again
>
> Chip Pearson wrote:
> > Home =
> > Cells(ActiveCell.Row,"A").Select
> >
> > Ctrl+Home=
> > Range("A1").Select
> >
> >
>

Well that was obvious!
But seriously. Thanks a lot.

Charlie wrote:
> It took me forever and a day to find this one...
>
> Cells(ActiveWindow.ActivePane.ScrollRow,
> ActiveWindow.ActivePane.ScrollColumn).Select
>
>
>
> "noyb" wrote:
>
>
>>Thanks, but, let me restate. I have a number of worksheets I want to
>>cycle through and get the selected cell to the top left of the
>>non-frozen part of each sheet. The number of frozen rows and columns
>>differs from sheet to sheet. Is it possible to so this without having to
>>hard code the location for each sheet. ie: the vba equivalent of
>>"Ctrl+Home" with frozen panes.
>>Thanks again
>>
>>Chip Pearson wrote:
>>
>>>Home =
>>>Cells(ActiveCell.Row,"A").Select
>>>
>>>Ctrl+Home=
>>>Range("A1").Select
>>>
>>>
>>

Charlie,

This takes you to the top left of the "displayed" page but does not force to
the same place that <Ctrl> <Home> does (the top/left corner of the "unfrozen"
pane). My macro hides/reveals certain columns and I want to set the cursor
the top/left of the "unfrozen" window as determined by the "Freeze Panes"
command (i.e. regardless of where the cursor has been scrolled to and
regardless of the presence/absence of hidden columns).

I have tried to use SendKeys but am having difficulty get the code to work
reliably given my limited knowledge/experience.

Anyone have a suggestion?

"Charlie" wrote:

> It took me forever and a day to find this one...
>
> Cells(ActiveWindow.ActivePane.ScrollRow,
> ActiveWindow.ActivePane.ScrollColumn).Select
>
>
>
> "noyb" wrote:
>
> > Thanks, but, let me restate. I have a number of worksheets I want to
> > cycle through and get the selected cell to the top left of the
> > non-frozen part of each sheet. The number of frozen rows and columns
> > differs from sheet to sheet. Is it possible to so this without having to
> > hard code the location for each sheet. ie: the vba equivalent of
> > "Ctrl+Home" with frozen panes.
> > Thanks again
> >
> > Chip Pearson wrote:
> > > Home =
> > > Cells(ActiveCell.Row,"A").Select
> > >
> > > Ctrl+Home=
> > > Range("A1").Select
> > >
> > >
> >