VBA Range OFFSET - Excel Champs

Skip to content Tutorials

• Excel Basics
• Keyboard Shortcuts
• Excel Tips
• Excel Functions
• Excel Formulas
• Advanced Excel
• Pivot Table
• VBA
• Power‌ Query

Home / VBA / Excel VBA Offset – How to use it

- Written by Puneet Gogia (Microsoft MVP)

Contents hide What is VBA OFFSET? OFFSET Syntax Use OFFSET in VBA Examples to use OFFSET in VBA Simple Navigation Example Using OFFSET with ActiveCell Using OFFSET with ActiveCell to Select a Range Copy a Range using OFFSET Using Cells Property with OFFSET Copy and Paste using Offset Moving and Changing Cell Color with OFFSET Looping Through Columns with Offset Using Offset with a Named Range Using Offset to Identify Last Used Cell Using Offset in a Nested Loop Conditional Formatting with Offset Filling a Range with an Incrementing Number Copying a Dynamic Range Based on Criteria Finding the Maximum Value in an Offset Range Inserting Rows Based on Offset Using Offset to Sum Alternate Rows Dynamic Range Selection Based on User Input Related Tutorials

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

1. Specify the range from where you want to start.
2. Enter a dot (.) to get a list of properties and methods.
3. Select the offset property and specify the arguments (row and column).
4. 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

Related Tutorials

• Find Last Row, Column, and Cell using VBA in Excel
• Select a Range/Cell using VBA in Excel
• SELECT ALL the Cells in a Worksheet using VBA
• UsedRange Property in VBA in Excel

• VBA Copy Range to Another Sheet + Workbook

About Excel Champs

Excel Champs is one of the top Excel blogs where you can find some of the most amazing tutorials on Excel basics, formulas, VBA, charts, and much more...

Contact - Terms - Policy

Quick Links

• Free Online Excel Courses
• Excel Blog
• Excel Inventory Template
• Testimonials

Excel Guides

• How to use Excel
• Describe Your Excel Skills in Resume
• Excel for Accountants
• Excel Tips and Tricks
• What is VBA
• Pivot Tables in Excel
• Excel Skills