Offset Property in VBA Excel | debugvba.com

offset vba excel

Offset property returns a range object with reference to a specified range. We can refer to a cell with respect to the position of another cell. Position can be up/down from a row or right/left from a column.

Syntax

expression.Offset (RowOffset, ColumnOffset)

Here expression is a range object e.g. Range(“A1”)

For example, we can access a cell which is 2 rows down and 1 column right with respect to cell A1.In the below shown example, we have selected the cell A1 at the begining, and there after we have used offset property of a range.

In the below shown code snippet, first we have selected the cell A1, so this becomes active cell. Then we have used the offset property. Offset (3,2) means that it selects the cell which is 3 rows downwards and 2 columns on right from cell A1. C4 will be new active cell now.We shall change the value in cell C4 as “Value_1” and change the colour as yellow. It is shown in the image below.

 

Range("A1"). Select

ActiveCell.Offset(3, 2). Select

ActiveCell.Value = "Value_1"

ActiveCell.Interior.Color = vbYellow

 

 

offset vba excel

 

 

offset vba excel

 

 

Once we execute above mentioned line of codes, active cell is changed to C4. Now, we have used Offset (1, 0) property, which means that we shall refer to 1 row downwards, and column will remain the same. So, our new referenced cell will be C5. We have changed the colour and value in cell C5. We can see the change in the image below.

 

ActiveCell.Offset(1, 0).Select

ActiveCell.Value = "Value_2"

ActiveCell.Interior.Color = vbYellow

 

offset vba excel

 

Once we execute the above code C5 will become active cell. In the below shown statement 0 means we shall select the cell in same row, and 2 means two columns on the right. Now we shall refer to cell E5.

 

ActiveCell.Offset(0, 2).Select

ActiveCell.Value = "Value_3"

ActiveCell.Interior.Color = vbYellow

 

offset vba excel

Now E5 will become the active cell after executing the lines mentioned above. In the below shown statement -1 means one row upwards and 2 means 2 columns on the right from active cell. So, we shall refer to G4 now.

 

ActiveCell.Offset(-1, 2).Select

ActiveCell.Value = "Value_4"

ActiveCell.Interior.Color = vbYellow

 

 

 

offset vba excel

 

Now G4 is the new active cell. Here -1 means one row upwards and -2 means two columns on the left. So E3 will be referenced and it will become new active cell.

 

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

ActiveCell.Value = "Value_5"

ActiveCell.Interior.Color = vbYellow

 

 

offset vba excel

 

We have demonstrated the code snippets to explain the concept of Offset. Consolidated code is shown below, it contains all the code snippets mentioned above.

 

Sub Offset()

'Select the cell A1
'It will select the cell on 3 rows down and 2 columns to right side
'Active cell will be C4 now

Range("A1").Select

ActiveCell.Offset(3, 2).Select
ActiveCell.Value = "Value_1"
ActiveCell.Interior.Color = vbYellow


'It will select the cell on 1 rows down and no change in column
'Active cell will be C5 now

ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Value_2"
ActiveCell.Interior.Color = vbYellow


'It will select the cell on 2 colunn on right and no change in the row
'Active cell will be E5 now

ActiveCell.Offset(0, 2).Select
ActiveCell.Value = "Value_3"
ActiveCell.Interior.Color = vbYellow


'It will select the cell on 1 row on up side and 2 columns on right
'Active cell will be G4 now

ActiveCell.Offset(-1, 2).Select
ActiveCell.Value = "Value_4"
ActiveCell.Interior.Color = vbYellow

'It will select the cell on 1 row on up side and 2 columns on left side
'Active cell will be E3 now

ActiveCell.Offset(-1, -2).Select
ActiveCell.Value = "Value_5"
ActiveCell.Interior.Color = vbYellow


End Sub

 

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.range.offset

Post you may like

Insert Formula using VBA Excel