Top Left Cell

[Ed Davis]

I want the range to start at the top left cell and have been unable to do
this.
My code first selects a range off the sheet and then the range that I want
at the top left.

Range("a99").select
Range("a67").select

I have tried just the following but does not do it.
Range("A67").select

 

[Rick Rothstein]

It is not clear to me what you are ultimately looking to do. If you want to
select the cell that is currently located in the upper left corner of the
active worksheet, you could do this...

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

With that said, it is rarely necessary to select any cells in order to
manipulate them. If you told us exactly what you are trying to do, perhaps
someone here can offer you a coded solution or guideline.

 

[Ed Davis]

I am trying to get the area that I want to work in to start at the top left
corner rather than in the middle of the sheet.


--
Thank You in Advance
Ed Davis

It is not clear to me what you are ultimately looking to do. If you want
to select the cell that is currently located in the upper left corner of
the active worksheet, you could do this...

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

With that said, it is rarely necessary to select any cells in order to
manipulate them. If you told us exactly what you are trying to do, perhaps
someone here can offer you a coded solution or guideline.

 

[Rick Rothstein]

Let's say the cell you want to appear in the upper left corner of the active
worksheet is A67, then execute these two statements (adjust the column and
row numbers to suit your needs)...

ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 67

--
Rick (MVP - Excel)

I am trying to get the area that I want to work in to start at the top left
corner rather than in the middle of the sheet.

 

[Ed Davis]

Thank You
That did it.

 

[Max]

Rick,
If I have a cell ref input in say, B2: C5
how could I scroll the sheet using a sub so that C5 is the top left cell?
Thanks

 

[Rick Rothstein]

Something like this I suppose...

ActiveWindow.ScrollColumn = Range(Range("B2").Value).Column
ActiveWindow.ScrollRow = Range(Range("B2").Value).Row

Although you might need error checking code if it is possible for B2 to not
have the text for a valid cell address in it.

 

[Max]

Thanks, Rick. That example works fine.

 

[Dave Peterson]

Another way:

Option Explicit
Sub testme()

Dim TestRng As Range

With ActiveSheet
Set TestRng = Nothing
On Error Resume Next
Set TestRng = .Range(.Range("B2").Value)
On Error GoTo 0

If TestRng Is Nothing Then
MsgBox "Not a valid range"
Else
Application.Goto TestRng, scroll:=True
End If
End With
End Sub

 

[Max]

Thanks, Dave. That works fine too.