Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating
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 -----Original Message----- I assume you are working in VBA. currentRow = ActiveCell.Row currentColumn = ActiveCell.Column Next Row: Cells(currentRow + 1, currentColumn).Activate Next Column: Cells(currentRow, currentColumn + 1).Activate Previous Row: Cells(currentRow - 1, currentColumn).Activate Previous Column: Cells(currentRow, currentColumn - 1).Activate Does this help? Ryan -----Original Message----- 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 . . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating
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 -----Original Message----- I assume you are working in VBA. currentRow = ActiveCell.Row currentColumn = ActiveCell.Column Next Row: Cells(currentRow + 1, currentColumn).Activate Next Column: Cells(currentRow, currentColumn + 1).Activate Previous Row: Cells(currentRow - 1, currentColumn).Activate Previous Column: Cells(currentRow, currentColumn - 1).Activate Does this help? Ryan -----Original Message----- 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 . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating
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. -----Original Message----- 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 -----Original Message----- I assume you are working in VBA. currentRow = ActiveCell.Row currentColumn = ActiveCell.Column Next Row: Cells(currentRow + 1, currentColumn).Activate Next Column: Cells(currentRow, currentColumn + 1).Activate Previous Row: Cells(currentRow - 1, currentColumn).Activate Previous Column: Cells(currentRow, currentColumn - 1).Activate Does this help? Ryan -----Original Message----- 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 . . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating
Typo, sorry. Missed a currentColumn in there, but you
probably caught that. -----Original Message----- 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. -----Original Message----- 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 -----Original Message----- I assume you are working in VBA. currentRow = ActiveCell.Row currentColumn = ActiveCell.Column Next Row: Cells(currentRow + 1, currentColumn).Activate Next Column: Cells(currentRow, currentColumn + 1).Activate Previous Row: Cells(currentRow - 1, currentColumn).Activate Previous Column: Cells(currentRow, currentColumn - 1).Activate Does this help? Ryan -----Original Message----- 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 . . . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating
Move one cell to the left:
activecell.offset(1,0).select Move one cell to the right: Move one cell to the left: In , Hafeez Esmail typed: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating
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 -- HTH, Dianne In , Hafeez Esmail typed: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating
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 , Hafeez Esmail typed: 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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating
And looking at your first post, I thought you were giving dancing lessons
<g -- Regards, Tom Ogilvy "Dianne" wrote in message ... 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 -- HTH, Dianne In , Hafeez Esmail typed: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating
What is VBA for "shake it all about"? <g
-- Dianne a.k.a. Hokey Pokey Instructor In , Tom Ogilvy typed: And looking at your first post, I thought you were giving dancing lessons <g "Dianne" wrote in message ... 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 -- HTH, Dianne In , Hafeez Esmail typed: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating
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! -----Original Message----- What is VBA for "shake it all about"? <g -- Dianne a.k.a. Hokey Pokey Instructor In , Tom Ogilvy typed: And looking at your first post, I thought you were giving dancing lessons <g "Dianne" wrote in message ... 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 -- HTH, Dianne In , Hafeez Esmail typed: 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 . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating
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. -- HTH, Dianne In , Hafeez Esmail typed: 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" wrote in message ... 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 -- HTH, Dianne In , Hafeez Esmail typed: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Navigating | Excel Worksheet Functions | |||
Navigating | Excel Worksheet Functions | |||
navigating with the tab key | New Users to Excel | |||
Navigating Excelbanter | Excel Discussion (Misc queries) | |||
Navigating | Excel Discussion (Misc queries) |