Using OFFSET with the range object, you can navigate from one cell to another in the worksheet and you can also select a cell or a range. It also gives you access to the properties and methods that you have with the range object to use, but you need to specify the arguments in the OFFSET to use it.
Use OFFSET with the Range Object
- 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.
Select a Range using OFFSET
You can also select a 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.
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 the cell A1 (as you have specified) with row one and column one using the cells property, and then uses the offset property to selects the cell which is two rows down and three columns.
More on VBA Range and Cells
- How to Set (Get and Change) Cell Value using a VBA Code
- How to Sort a Range using VBA in Excel
- How to Create a Named Range using VBA (Static + Dynamic) in Excel
- How to Merge and Unmerge Cells in Excel using a VBA Code
- How to Check IF a Cell is Empty using VBA in Excel
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- Excel VBA Font (Color, Size, Type, and Bold)
- How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- How to Copy a Cell\Range to Another Sheet using VBA
- How to use Range/Cell as a Variable in VBA in Excel
- How to Find Last Rows, Column, and Cell using VBA in Excel
- How to use ActiveCell in VBA in Excel
- How to use Special Cell Method in VBA in Excel
- How to Apply Borders on a Cell using VBA in Excel
- How to Refer to the UsedRange using VBA in Excel
- How to Change Row Height/Column Width using VBA in Excel
- How to Select All the Cells in a Worksheet using a VBA Code
- How to Insert a Row using VBA in Excel
- How to Insert a Column using VBA in Excel