Forum Discussion

lingyai's avatar
lingyai
Copper Contributor
Jun 30, 2019
Solved

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

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'...
Macros and VBA
  • Brad_Yundt's avatar
    Brad_Yundt
    Jul 12, 2019

    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")))
Brad_Yundt's avatar
Brad_Yundt
MVP
Jul 12, 2019

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")))
lingyai's avatar
lingyai
Copper Contributor
Jul 12, 2019

Brad_Yundt  thanks very much!

Resources