SOLVED

VBA code to move fr. active cell to single cell X rows/Y columns away, selecting nothing in between?

Copper Contributor

This is surprsingly tricky.

Let's say I have two named cells:
"Move_this_many_rows" -- let's say for example it contains a 3
"Move_this_many_columns" -- let's say for example it contains a 4

Let's also say that the active cell is A1.

When I run the following code, it expands the selection, from A1:E4. This part is fine.

 

 

Dim StartingCell As Range, EndingCell As Range
Set StartingCell = ActiveCell
Set EndingCell = Range(StartingCell, StartingCell.Offset(Range("Move_this_many_rows"), Range("Move_this_many_columns")))
EndingCell.Select

 

 

But it ends with A1:E4 selected, whereas what I want is for only E4 to be selected. And I can't figure out how to get from the state of slecting A1:E4 to just selecting E4.

(By the way, the space I'm working in might have rows/ columns around it, so none of these cells will be the last ones used on the sheet.)

Frustrating, as it's so simple

-- to write the worksheet version of the OFFSET formula I want -- if I want to move 3 rows down and 4 columns to the right, 3 and 4 are the OFFSET function's 2nd and 3rd arguments.

-- manually with the macro recorder. I know I want to just move 3 rows down and 4 to the right, the code is
ActiveCell.Offset(3, 4).Range("A1").Select

I've got two named cells, one containing the 3 and the other, the 4, yet I can't seem to refer to them in this simple line of code.

Any advice?

2 Replies
best response confirmed by lingyai (Copper Contributor)
Solution

@lingyai 

Sub Offsetting()
ActiveCell.Offset(Range("Move_this_many_rows").Value, Range("Move_this_many_columns").Value).Select
End Sub

I rewrote your code as a one-liner to move the active cell.

 

Your code was creating a range object spanning the cells between StartingCell and EndingCell. Actually, you just needed the part that begins StartingCell.Offset(...

Set EndingCell = Range(StartingCell, StartingCell.Offset(Range("Move_this_many_rows"), Range("Move_this_many_columns")))
1 best response

Accepted Solutions
best response confirmed by lingyai (Copper Contributor)
Solution

@lingyai 

Sub Offsetting()
ActiveCell.Offset(Range("Move_this_many_rows").Value, Range("Move_this_many_columns").Value).Select
End Sub

I rewrote your code as a one-liner to move the active cell.

 

Your code was creating a range object spanning the cells between StartingCell and EndingCell. Actually, you just needed the part that begins StartingCell.Offset(...

Set EndingCell = Range(StartingCell, StartingCell.Offset(Range("Move_this_many_rows"), Range("Move_this_many_columns")))

View solution in original post