Top Left Cell

  • Thread starter Thread starter Ed Davis
  • Start date Start date
E

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
 
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.
 
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
Rick Rothstein said:
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.
Click to expand...
 
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)


Ed Davis said:
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.
Click to expand...
 
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
 
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.
 
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
 
Back
Top