How to use the RANGE.OFFSET property
Author: Oscar Cronquist Article last updated on August 31, 2018
The OFFSET property returns a Range object based on a row and column number. The image above shows a macro that uses cell E7 and applies an offset with 6 rows and 4 columns. The Msgbox displays the address of the Range object.
Excel Property Syntax
expression.Offset(RowOffset, ColumnOffset)
Arguments
| expression | Required. Range object. |
| RowOffset | Optional. How many rows the range object is to be offset. If omitted 0 (zero) is used. The argument may be a positive or negative value. |
| ColumnOffset | Optional. How many columns the range object is to be offset. If omitted 0 (zero) is used. The argument may be a positive or negative value. |
Comments
It is possible to offset a cell range containing multiple cells as well. The image below demonstrates the OFFSET property with a ColumnOffset argument of -2.
RANGE.OFFSET property examples
Add or remove a value in a drop down list programmatically
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]
Add values to a two-dimensional table based on conditions [VBA]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]
Consolidate sheets [vba]
Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]
Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]
Copy data from workbooks in folder and subfolders
I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by […]
I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by […]
Create links to all sheets in a workbook programmatically
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]
Excel template: Getting Things Done [VBA]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
Extract cell references populated with values [VBA]
This article demonstrates a macro that returns cell references for cell ranges populated with values on a worksheet. Jinesh asks: […]
This article demonstrates a macro that returns cell references for cell ranges populated with values on a worksheet. Jinesh asks: […]
Hide specific columns programmatically
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
How to copy non contiguous cell ranges
If you try to copy multiple cell ranges on a worksheet that don't have the same number of rows or […]
If you try to copy multiple cell ranges on a worksheet that don't have the same number of rows or […]
How to create a list of comments from a worksheet programmatically
Did you know that you can select all cells containing comments in the current sheet? Press F5, press with left […]
Did you know that you can select all cells containing comments in the current sheet? Press F5, press with left […]
How to use the FIX function [VBA]
The FIX function removes the decimals from the argument. Excel Function VBA Syntax Fix(number) Arguments number Required. Is a Double or […]
The FIX function removes the decimals from the argument. Excel Function VBA Syntax Fix(number) Arguments number Required. Is a Double or […]
How to use the INT function [VBA]
The INT function removes the decimals if the numeric value is above 0 (zero) and returns a negative integer less than […]
The INT function removes the decimals if the numeric value is above 0 (zero) and returns a negative integer less than […]
How to use the RANGE.FIND method
The Range.Find method returns a range object for the first cell that matches the lookup value. Macro used in workbook above The […]
The Range.Find method returns a range object for the first cell that matches the lookup value. Macro used in workbook above The […]
How to use the SGN function [VBA]
The SGN function returns an integer that shows the sign of the number. Argument SGN returns Number greater than 0 (zero). […]
The SGN function returns an integer that shows the sign of the number. Argument SGN returns Number greater than 0 (zero). […]
List all hyperlinks in worksheet programmatically
The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]
The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]
List files in folder and create hyperlinks (VBA)
Table of Contents List files in a folder and create hyperlinks (VBA) How to navigate quickly in a complex workbook […]
Table of Contents List files in a folder and create hyperlinks (VBA) How to navigate quickly in a complex workbook […]
Locate a shape in a workbook
This article demonstrates how to locate a shape in Excel programmatically based on the value stored in the shape. The […]
This article demonstrates how to locate a shape in Excel programmatically based on the value stored in the shape. The […]
Move a shape [VBA]
This article demonstrates how to move a shape, a black arrow in this case, however, you can use whatever shape […]
This article demonstrates how to move a shape, a black arrow in this case, however, you can use whatever shape […]
Multi-level To-Do list template
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]
Normalize data [VBA]
To be able to use a Pivot Table the source data you have must be arranged in way that a […]
To be able to use a Pivot Table the source data you have must be arranged in way that a […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Prepare data for Pivot Table – How to split concatenated values?
This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]
This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]
Rearrange values based on category [VBA]
In this post I am going to rearrange values from a list into unique columns. Before: After: The code Get […]
In this post I am going to rearrange values from a list into unique columns. Before: After: The code Get […]
Search all workbooks in a folder
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]
Search all workbooks in a folder and sub folders – VBA
This article demonstrates macros that allow you to search for a text string(s) in multiple worksheets and workbooks located in […]
This article demonstrates macros that allow you to search for a text string(s) in multiple worksheets and workbooks located in […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Which Excel files in folder are password protected?
This article explains how to check if Excel files in a given folder are password protected. The image above shows […]
This article explains how to check if Excel files in a given folder are password protected. The image above shows […]
Working with FILES
In this blog article, I will demonstrate basic file copying techniques using VBA (Visual Basic for Applications). I will also […]
In this blog article, I will demonstrate basic file copying techniques using VBA (Visual Basic for Applications). I will also […]
This extensive vba reference list provides detailed information including syntax, arguments, return values, and examples for Visual Basic for Applications.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form