Navigating

[Hafeez Esmail]

I've looked all over for this info but can't find it.

If my active cell is H396, how would I do the following?
Move one cell to the left/right/up/down?
Move to the bottom of this column?
Move to the end of this row?

Thanks
Hafeez Esmail

 

[Ryan]

Sorry, I forgot about your other questions.

To move to the end of the current column:
Cells(65536, currentColumn).Activate

To move to the end of the current row:
Cells(currentRow, 256).Activate

 

[Hafeez Esmail]

Hi Ryan
Your assumption is correct; I am working in VBA.

Let's say crntRow = 10 and crntCol = H
Therefore Cells(crntRow + 1, crntCol).Activate is H11
Now if I want to move to H12, is that
Cells(crntRow + 1, crntCol).Activate or is it
Cells(crntRow + 2, crntCol).Activate?

And also, is there a shortcut way of moving to the end of
a row or column?

One last thing is that I saw the following constants in a
piece of code but don't know if they apply or how to use
them, xlDown, xlUp, xlToLeft, xlToRight, xlRight, xlLeft

Thanks for your help!
Hafeez Esmail

 

[Guest]

The crntRow and crntCol are just a reference to whatever
the cell was when you set them (crntRow =
ActiveCell.Row). If you do something like Cells(crntRow
+ 1, crntCol).Activate, it will activate the next cell in
that row. It will not change the reference cell,
however. If you want to change the reference cell, so
that moving one more row down would still involve a
crntRow + 1, you have to update the reference cell to be
equal to the currently selected cell. Here is an example.

currentRow = ActiveCell.Row
currentColumn = ActiveCell.Column

Cells(currentRow + 1).Activate
Cells(currentRow + 2).Activate

The above is the same as doing this:

currentRow = ActiveCell.Row
currentColumn = ActiveCell.Column

Cells(currentRow + 1).Activate

' set the new reference cell
currentRow = ActiveCell.Row
currentColumn = ActiveCell.Column

Cells(currentRow + 1, currentColumn).Activate

Don't think of currentRow and currentColumn as the
parameters for the cell that is active. Think of them as
the parameters for the cell that WAS active when you
defined them.

 

[Guest]

Typo, sorry. Missed a currentColumn in there, but you
probably caught that.

 

[Dianne]

Move one cell to the left:
activecell.offset(1,0).select
Move one cell to the right:

Move one cell to the left:



In

 

[Dianne]

Sorry about the first post -- tripped over my fingers:

One cell left:
ActiveCell.Offset(0,-1).Select

One cell right:
ActiveCell.Offset(0,1).Select

One cell down:
ActiveCell.Offset(1,0).Select

One cell up:
ActiveCell.Offset(-1,0).Select

Bottom of the column (assuming there is data in every row):
ActiveCell.End(xlDown).Select

End of row (assuming there is data in each column):
ActiveCell.End(xlToRight).Select

 

[Ryan]

That is a much better solution.

-----Original Message-----
Move one cell to the left:
activecell.offset(1,0).select
Move one cell to the right:

Move one cell to the left:



In


.

 

[Tom Ogilvy]

And looking at your first post, I thought you were giving dancing lessons
<g>

 

[Dianne]

What is VBA for "shake it all about"? <g>

 

[Hafeez Esmail]

Out of all the solutions I think I like this one the best!
I did note the clause when selecting the last cell. This
leads me to one last question. How do you select the last
cell in a row/column that HAS data?

Thanks for all your help!

 

[Dianne]

Hafeez,

End(xlDown) or End(xlToRight) behave in exactly the same way as if you
were using the End key followed by the Down Arrow key or the Right Arrow
key. Let's say you're using ActiveCell.End(xlDown).Select and you have a
column of data like this:

Hello
Hi
<blank>
Howdy
How are you
Hooray
<blank>
Huzzah

Where you end up in this column depends on where you start. If you start
at Hello, you'll end up at Hi (because there's a blank cell as the next
cell). If you start at Howdy, you'll end up at Hooray.

If every single cell in the column has data/formulas, when you do
End(xlDown), then you will end up on the cell in the last row.

So you have to be really careful when using End(xlDown), because if you
do have blanks, you won't necessarily get the last row.

What I usually do is to use End(xlUp). If you start at A65535 and do
End(xlUp), you should end up in the last row of data (unless of course
the last row of data has a blank cell in column A).

Anyway, experiment a bit with the keyboard equivalents and make sure you
understand how they work. Then just translate them into VBA --
End(xlDown) etc.