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
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
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
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
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
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
