Navigating Excel Cells in VBA

Fixed Reference

There’s plenty of documentation on how to move around Excel in VBA if you know exactly what cell you want to move to.

For example to move to cell D3 all you need to do is add this line of code:

ActiveSheet.Range("D3").Select

Or let’s say you want to select not just a single cell but a range of cells, like D3 thru E4:

ActiveSheet.Range("D3:E4").Select

Relative Reference

But let’s say you want to run your macro from anywhere on the spreadsheet instead of a fixed location.

For example, you’re cursor is on some cell and you want to move left 2 columns and up 1 row:

ActiveCell.Offset(-1, -2).Select

Or maybe instead you to stay on the same row, but move to the right 5 columns:

ActiveCell.Offset(0, 5).Select

Now let’s say you want to move left 9 columns and up one row.  Once you get there you want to select from the new starting cell to one cell to the right and one row down so that four cells are selected:

ActiveCell.Offset(-1, -9).Select
Range(ActiveCell, ActiveCell.Offset(1, 1)).Select

Now just imagine that once you’ve selected those four cells what you really want to do is copy the values, or the formulas, from the top two cells to the bottom two cells.  Enter the following code all on one line:

Selection.AutoFill Destination:=Range(ActiveCell, _
ActiveCell.Offset(1, 1)), Type:=xlFillDefault

If you need to fill more rows then just extend the range by changing the row offset to whatever number you need.

The Function

ActiveSheet.Range("{cell}").Select
ActiveCell.Offset({row}, {column}).Select

Leave a Reply