How to use OFFSET range in Excel VBA

The Offset is another way to select a single cell or a group of cells as a range. It is used in Range property. For better understanding of Offset property I'm going to explain few code:


Offset Example 1:

Sub offsets()
     Range("A1").Offset(13, 2).Select
End Sub

The above code will select C14 cell. The reference location here used is cell A1. After mentioned the range A1 (single cell), offset starts. The first part of Offset property is jump_to_row and second part is jump_to_column. Here I've mentioned 13 rows need to jump from A1 row. So final row number is 1+13 = 14 that means, A14. And the jump_to_column is 2. So 1+2 = 3. That means C. The final location is C14.

Image 1: Code 1

Offset Example 2:

Sub offsetinvba()
      Range("D5").Offset(-3, -2).Select
End Sub

This code will select B2 cell. D5 is the reference cell. And from this D5 cell 3 rows back. That means 5-3 = 2. For column, currently selected D which is 4th column, need to back -2 means 4-2 = 2 which is B column. The final location is B2.

Image 2: Code 2

Offset Example 3:

Sub offsets()
      Range("A1:D3").Offset(4, 2).Select
End Sub

This code will select (3 rows x 4 columns) as selected in reference A1:D3 (3 rows and 4 columns). Now what will be the range where offset(4,2)? New location is 1+4 = 5, A+2 = C means C5. And D3 means 3+2 = 5, D+2 = F, means F5. So, C:F selected which is 4 columns. The reference was 3 rows.

 

Image 3: Code 3

By the way you don't need to  remember all these offset logic. Simply you can change the range of offset and observe the result. By this way you can set your desire offset range in Excel VBA Environment.