Thread: Set Visible Range

I can detect the visible range in the active worksheet with:

Sub Macro1()
Dim r As Range
Dim s As String
Set r = ActiveWindow.VisibleRange
s = r.Address
MsgBox (s)
End Sub

When, however, I try to setup the visible range with:

Sub Macro2()
Dim s As String
s = "$A$1:Z$100"
ActiveWindow.VisibleRange = Range(s)
End Sub

Nothing happens.

How can I adjust the scrollbars and zoom so that the upper left-hand cell is
A1 and the lower right-hand cell is Z100 using VBA?

Thanks in Advance
--
Gary's Student

Range("A1:Z100").Select
ActiveWindow.Zoom = True

Excel will do the best it can to meet that - as a minimum, the range will be
visible and at least one constraint will be bounding - but that rectangle
may not meet the dimensions of your computers resolution or the Excel Window
size if your a windowed.

--
Regards,
Tom Ogilvy



"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:FC46D85F-A039-4DA8-9911-218F6C0ED54D@microsoft.com...
> I can detect the visible range in the active worksheet with:
>
> Sub Macro1()
> Dim r As Range
> Dim s As String
> Set r = ActiveWindow.VisibleRange
> s = r.Address
> MsgBox (s)
> End Sub
>
> When, however, I try to setup the visible range with:
>
> Sub Macro2()
> Dim s As String
> s = "$A$1:Z$100"
> ActiveWindow.VisibleRange = Range(s)
> End Sub
>
> Nothing happens.
>
> How can I adjust the scrollbars and zoom so that the upper left-hand cell
is
> A1 and the lower right-hand cell is Z100 using VBA?
>
> Thanks in Advance
> --
> Gary's Student

VisibleRange is a read-only property.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:FC46D85F-A039-4DA8-9911-218F6C0ED54D@microsoft.com...
> I can detect the visible range in the active worksheet with:
>
> Sub Macro1()
> Dim r As Range
> Dim s As String
> Set r = ActiveWindow.VisibleRange
> s = r.Address
> MsgBox (s)
> End Sub
>
> When, however, I try to setup the visible range with:
>
> Sub Macro2()
> Dim s As String
> s = "$A$1:Z$100"
> ActiveWindow.VisibleRange = Range(s)
> End Sub
>
> Nothing happens.
>
> How can I adjust the scrollbars and zoom so that the upper left-hand cell
is
> A1 and the lower right-hand cell is Z100 using VBA?
>
> Thanks in Advance
> --
> Gary's Student

Thank you Tom
Worked like a charm
--
Gary's Student


"Tom Ogilvy" wrote:

> Range("A1:Z100").Select
> ActiveWindow.Zoom = True
>
> Excel will do the best it can to meet that - as a minimum, the range will be
> visible and at least one constraint will be bounding - but that rectangle
> may not meet the dimensions of your computers resolution or the Excel Window
> size if your a windowed.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
> news:FC46D85F-A039-4DA8-9911-218F6C0ED54D@microsoft.com...
> > I can detect the visible range in the active worksheet with:
> >
> > Sub Macro1()
> > Dim r As Range
> > Dim s As String
> > Set r = ActiveWindow.VisibleRange
> > s = r.Address
> > MsgBox (s)
> > End Sub
> >
> > When, however, I try to setup the visible range with:
> >
> > Sub Macro2()
> > Dim s As String
> > s = "$A$1:Z$100"
> > ActiveWindow.VisibleRange = Range(s)
> > End Sub
> >
> > Nothing happens.
> >
> > How can I adjust the scrollbars and zoom so that the upper left-hand cell
> is
> > A1 and the lower right-hand cell is Z100 using VBA?
> >
> > Thanks in Advance
> > --
> > Gary's Student
>
>
>