Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later 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-right 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 (2298) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Relative VBA Selections.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Import a bunch of ZIP Codes into Excel, and you may be surprised that any leading zeroes disappear. Here's a handy little ...
Discover MoreIf someone sends you a worksheet that has lots of data in it, you might want to "spread out" the data so you can have ...
Discover MoreAs your macro processes information in a worksheet, you may want to make sure that it skips over rows that are hidden. ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-05-31 17:58:53
abh
what about to obtain the current "active cell" inside another "active cell" (a selected range of cells)???
excel can't fire an event when the cell position inside a range of selected cells is changing because the ENTER or TAB keys are pressed
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2018 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: 
Comments