Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Relative VBA Selections.
It is a common thing to need to select cells in a macro. What if you want to select a range of cells relative to your current location, however? It so happens that there are several ways you can accomplish this task. For instance, if you want to select a single cell, relative to your current location, you can use the Offset method. As an example, if you want to select the cell that is two rows down and one column to the right of your current location, you could use the following:
ActiveCell.Offset(2, 1).Select
If you want to select a larger range than just a single cell, you can combine the Offset method with the Address Method to find actual cell addresses, and then use your findings to actually select the range itself. For instance, you might want to select the range that begins two rows down and one column to the right, but then extends for four rows and three columns. You can accomplish this in the following manner:
StartCell = ActiveCell.Offset(2, 1).Address EndCell = ActiveCell.Offset(5, 3).Address Range(StartCell, EndCell).Select
An alternative method of accomplishing the same task is to use the Resize method. In this technique, you would first select the upper-left cell of the desired range (as was done in the first use of Offset, above), and then use Resize to change the size of the selection. This is how it is done:
ActiveCell.Offset(2, 1).Select Selection.Resize(4, 3).Select
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7016) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Relative VBA Selections.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Renaming a worksheet within a macro is a relatively easy task. When you start renaming based on a range of names, though, ...
Discover MoreIt is not uncommon to set variables in a macro based on other values, such as time or date. You could also set variables ...
Discover MoreWrite out a check and you need to include the digits for the amount of the check and the value of the check written out ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Comments