Excel VBA Offset - How to use it

What is VBA OFFSET?

In VBA, OFFSET allows you to move or refer from one cell to another by a specified number of rows and columns. For example, Range(“A1”).Offset(2, 1) moves two rows down and 1 column to the right, landing on cell B3.

You can do something with this new cell, like setting its value. Positive numbers move down and right, while negative numbers move up and left. It makes working with cells easy relative to a starting reference point.

OFFSET Syntax

Syntax of Offset in VBA

RangeObject.Offset(RowOffset, ColumnOffset)

RangeObject is the starting cell or range.
RowOffset is the number of rows that move from the starting cell. Positive number moves down; negative number moves up.
ColumnOffset is the number of columns that move from the starting cell. Positive numbers move right; negative number moves left.

Use OFFSET in VBA

Specify the range from where you want to start.
Enter a dot (.) to get a list of properties and methods.
Select the offset property and specify the arguments (row and column).
In the end, select property to use with the offset.

With this code, you can select range which is the number of rows and columns aways from a range. Take the below line of code, that selects a range of two cells which is five rows down and 3 columns right.

Range("A1:A2").Offset(3, 2).Select

Apart from that, you can also write code to select the range using a custom size. Take an example of the following code.

Range(Range("A1").Offset(1, 1), Range("A1").Offset(5, 2)).Select

To understand this code, you need to split it into three parts.

First thing first, in that range object, you have the option to specify the first cell and the last of the range.

Now let’s come back to the example:

In the FIRST part, you have used the range object to refer to the cell that is one row down and one column right from the cell A1.
In the SECOND part, you have used the range object to refer to the cell that us five rows down and two columns right from the cell A1.
In the THRID part, you have used the cells from the part first and second to refer to a range and select it.

Examples to use OFFSET in VBA

Next, we have a list of codes that use the OFFSET property to perform different activities in Excel. To use these codes, you can copy them and directly paste them into the Visual Basic Editor.

Simple Navigation Example

Sub Example1()
    Range("A1").Offset(2, 3).Value = "Moved to D3" 'Moving Down and Right
End Sub
Sub Example2()
    Range("C3").Offset(-1, -2).Value = "Moved to A2" 'Moving Up and Left
End Sub
Sub Example3()
    Range("B2").Offset(5, 0).Value = "Moved to B7" 'Moving Down Only
End Sub
Sub Example4()
    Range("D1").Offset(0, 4).Value = "Moved to H1" 'Moving Right Only
End Sub

Using OFFSET with ActiveCell

You can also use the active cell instead of using a pre-defined range. That means you’ll get a dynamic offset to select a cell navigating from the active cell.

ActiveCell.Offset(5, 2).Select

The above line of code will select the cell which is five rows down and two columns right from the active cell.

Using OFFSET with ActiveCell to Select a Range

Use the following code to select a range from the active cell.

Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(5, 2)).Select

To understand how this code works, make sure to see this explanation.

Copy a Range using OFFSET

Range(Range("A1").Offset(1, 1), Range("A1").Offset(5, 2)).Copy
Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(5, 2)).Copy

Using Cells Property with OFFSET

You can also use the OFFSET property with the CELLS property. Consider the following code.

Cells(1, 3).Offset(2, 3).Select

The above code first refers to cell A1 (as you have specified) with row one and column one using the cells property, and then uses the offset property to select the cell which is two rows down and has three columns.

Copy and Paste using Offset

Sub copy_and_paste_using_offset()
    Range("A1:A5").Copy Destination:=Range("A1").Offset(0, 2)
End Sub

Moving and Changing Cell Color with OFFSET

Sub moving_and_changing_cell_color_with_offset()
    With Range("A1").Offset(4, 3)
        .Value = "Colored Cell"
        .Interior.Color = RGB(0, 255, 0) ' Green background
    End With
End Sub

Looping Through Columns with Offset

Sub looping_through_columns()
    Dim i As Integer
    For i = 1 To 5
        Range("A1").Offset(0, i).Value = "Column " & i + 1
    Next i
End Sub

Using Offset with a Named Range

Sub offset_with_a_named_range()
    Range("StartCell").Offset(3, 2).Value = "Moved to E4"
End Sub

Using Offset to Identify Last Used Cell

Sub offset_to_identify_last_used()
    Dim lastCell As Range
    Set lastCell = Range("A1").End(xlDown).Offset(0, 1)
    MsgBox "The last cell in the adjacent column is " & lastCell.Address
End Sub

Using Offset in a Nested Loop

Sub offset_nested_loop()
    Dim i As Integer, j As Integer
    For i = 1 To 5
        For j = 1 To 5
            Range("A1").Offset(i, j).Value = "R" & i & "C" & j
        Next j
    Next i
End Sub

Conditional Formatting with Offset

Sub conditional_formatting_with_offset()
    Dim rng As Range
    Set rng = Range("B2:B10").Offset(0, 1)
    rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=100"
    rng.FormatConditions(rng.FormatConditions.Count).SetFirstPriority
    With rng.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407 ' Orange
    End With
End Sub

Filling a Range with an Incrementing Number

Sub filling_a_range_with_an_incrementing_number()
    Dim cell As Range
    Dim num As Integer
    num = 1
    For Each cell In Range("B2:F6").Offset(1, 1)
        cell.Value = num
        num = num + 1
    Next cell
End Sub

Copying a Dynamic Range Based on Criteria

Sub copying_a_dynamic_range_based_on_criteria()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A1:A" & lastRow).AutoFilter Field:=1, Criteria1:=">100"
    Range("A1:A" & lastRow).SpecialCells(xlCellTypeVisible).Copy Destination:=Range("B1").Offset(0, 1)
    ActiveSheet.AutoFilterMode = False
End Sub

Finding the Maximum Value in an Offset Range

Sub finding_the_maximum_value()
    Dim maxVal As Double
    maxVal = WorksheetFunction.Max(Range("A1:A10").Offset(0, 1))
    MsgBox "The maximum value in the offset range is " & maxVal
End Sub

Inserting Rows Based on Offset

Sub inserting_rows_based_on_offset()
    Dim cell As Range
    For Each cell In Range("A1:A10")
        If cell.Value = "Insert" Then
            cell.Offset(1, 0).EntireRow.Insert
        End If
    Next cell
End Sub

Using Offset to Sum Alternate Rows

Sub offset_to_sum_alternate_rows()
    Dim total As Double
    Dim i As Integer
    For i = 0 To 4
        total = total + Range("A1").Offset(i * 2, 0).Value
    Next i
    MsgBox "The sum of alternate rows is " & total
End Sub

Dynamic Range Selection Based on User Input

Sub Dynamic Range Selection()
    Dim rowNum As Long, colNum As Long
    Dim rng As Range
    
    rowNum = InputBox("Enter the number of rows to offset:")
    colNum = InputBox("Enter the number of columns to offset:")
    
    Set rng = Range("A1").Offset(rowNum, colNum).Resize(5, 5)
    rng.Value = "Selected Range"
End Sub

What is VBA

Excel VBA Offset – How to use it