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.
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.
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.
- Count Rows using VBA in Excel
- Excel VBA Font (Color, Size, Type, and Bold)
- Excel VBA Hide and Unhide a Column or a Row
- Excel VBA Range – Working with Range and Cells
- Apply Borders on a Cell using VBA in Excel
- Find Last Row, Column, and Cell using VBA in Excel
- Insert a Row using VBA in Excel
- Merge Cells in Excel using a VBA Code
- Select a Range/Cell using VBA in Excel
- SELECT ALL the Cells in a Worksheet using VBA
- ActiveCell in VBA in Excel
- Special Cells Method in VBA in Excel
- UsedRange Property in VBA in Excel
- VBA AutoFit (Rows, Column, or the Entire Worksheet)
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- VBA Copy Range to Another Sheet + Workbook
- VBA Enter Value in a Cell (Set, Get and Change)
- VBA Insert Column (Single and Multiple)
- VBA Named Range | (Static + from Selection + Dynamic)
- VBA Sort Range | (Descending, Multiple Columns, Sort Orientation
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- VBA Check IF a Cell is Empty + Multiple Cells