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: Selecting a Cell in the Current Row.

Selecting a Cell in the Current Row

by Allen Wyatt
(last updated October 26, 2021)

5

If you are developing Excel macros, you may wonder how you can select a cell relative to the one in which you are located. For instance, if you are using Excel and you press the Home key, the cell at the left side of the current row is selected. Unfortunately, using the macro recorder to record this does not help in this situation since it records destination of the action, instead of the your actual action. For instance, if you press Home and you are on the fourth row in a worksheet, Excel doesn't record the Home action, but instead records the destination, as follows:

Range("A4").Select

This is great if you always want to go to cell A4, but terrible if you want to go to the first cell of whatever row you are on.

As with many tasks in VBA, there are several ways you can approach a solution to this dilemma. The first method is actually a variation on what the macro recorder returns, as shown above. All you need to do is change the row designator so it represents the current row, as in the following:

Range("A" & (ActiveCell.Row)).Select

VBA figures out what the current row is, slaps it together with the "A" designator, and comes up with a cell reference that works with the Range method.

Another technique you can use is to put the Cells property to work, as follows:

Cells(Application.ActiveCell.Row, 1).Select

This approach, of course, can be modified so that you actually select any given cell in the current row. All you need to do is change the column designation (1, in the above example) to a number representing the column desired.

Another approach (which produces the same result) is to use the Range object in conjunction with the Cells property, as shown here:

Range(Cells(Selection.Row, 1).Address).Select

Selection.Row gives the row number of the current selection. The Address property of the Cells method returns the address of a particular cell in A$1$ format. This address is then used as the parameter for the Range object, and the actual cell is selected by the Select method.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11440) 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: Selecting a Cell in the Current Row.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Offering Options in a Macro

When creating macros, you often need to offer a series of choices to a user. This tip demonstrates how easy it is to ...

Discover More

Maintaining Delivery Address Information for Envelopes

Using the Envelopes and Labels dialog box to print envelopes is quick and easy. It can also be frustrating, however, ...

Discover More

Using Named Ranges in a Macro

Named ranges are a great capability provided by Excel. You can define all sorts of named ranges in a workbook, but how do ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Creating a Function Inventory for a Workbook

Your worksheets are very often made up of formulas and these formulas are made up of functions. If you ever want to ...

Discover More

Enforcing Moving Cells Up

When you design your worksheets, you probably want users to interact with those worksheets in specific ways. What ...

Discover More

Stepping Through a Non-Contiguous Range of Cells

Using macros to step through each cell in a selection is a common occurrence. What if that selected range is made up of ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 + 5?

2021-10-29 06:03:17

Alan Elston

@ Tomek
Nothing wrong with that way you showed, either.

Offset I find well suited to get a range of the same size as that range to which it is applied. I have found it an efficient , and tidy , way to get different columns into an array from a table, having just declared a single column ( typically column A ) as a range object.
I like the Range Item Property when dealing with/ wanting a single cell.
All just personal preferences, that’s all.
_.___________________
Just some other comments out of interest:
The Range Item Property in its two argument (row, column) form also will take a column Letter:
I have not seen this option used much, but just to demo the syntax, the code line to Select the first cell in the Current Row would be:
ActiveCell.EntireRow.Cells.Item(1, "A").Select
Note in that code line above , that the row 1 is relative to the top left cell of the row. Hence it is the current row. It is not the first row in the spreadsheet

One small subtle point related to some of the code line ideas that we have been discussing, that occasionally causes people to slip up, is the following, which is related to understanding what .Cells is about:
Excel seems to handle a single “width” type range, such as a row , a bit differently to how one often expects:
You could not use the two argument (row, column) form Range Item Property on ActiveCell.EntireRow without adding the extra .Cells.
The inclusion of .Cells gives us the range object that is all the cells in the row. Without that, extra .Cells, the range object is an entire row.
So this would select the next row,
ActiveCell.EntireRow.Item(2).Select
,wheras the following will select the second cell ( cell in column “B” ) , for the current row
ActiveCell.EntireRow.Cells.Item(2).Select

( The first code line there, ActiveCell.EntireRow.Item(2).Select , demos another interesting thing: The Range Item Property extends outside the range to which it is applied. That is a strange concept perhaps, but never the less, for the case of Excel ranges, that seems to be the case. I personally find that a very strange concept: It’s a bit like saying I have a bucket with 10 items in it, and the 11th item can be got at by referring to the 11th item of the bucket.
In that row example, I have a range object given by ActiveCell.EntireRow , which is a single row. But I can refer to its second item via ActiveCell.EntireRow.Item(2) , ( which returns me the next row ).
Strange…. , - but there you go , I have often thought that object orientated programming in Excel is anarchy, or a bit screwy

Alan Elston


2021-10-28 12:12:11

Tomek

How about:
ActiveCell.Offset(0, 1 - ActiveCell.Column).Select


2021-10-27 05:27:47

Alan Elston

Here is a couple of graphic representations of the Range Item Property --- --- https://excelfox.com/forum/showthread.php/2138-Understanding-VBA-Range-Object-Properties-and-referring-to-ranges-and-spreadsheet-cells?p=10035&viewfull=1#post10035


2021-10-27 05:05:17

Alan Elston

Hi Rick.
I also like to use that sort of syntax for referencing SINGLE cells.
I personally like to use a full syntax, - just a personal preference, to remind me that I am using the Range Item Property, which can be used to reference a single cell, relative to the top left of the range to which it is applied.
So in your example I like to write.
ActiveCell.EntireRow.Cells.Item(1).Select

One other interesting thing is that we can use negative numbers also. That would not work in that particular example for going back left , since ActiveCell.EntireRow has its top left cell in the first column: You are using in that example the single argument version of the Range Item Property, and using that, we go in the typical Excel convention of left to right, then a row down, then left to right, then a row down, then ….. and so on.
However we can use the two number argument version of the Range Item Property, .Item(row, column) , and could therefore select the cell in column A which is just above the current active row like this
ActiveCell.EntireRow.Cells.Item(0, 1).Select

Similarly this next example would select the cell in column A which is 2 cells up from the current active row
ActiveCell.EntireRow.Cells.Item(-1, 1).Select

( we “travel through the origin” when “going backwards” , as it were, so because Item(1, 1) is the top left cell of the range to which it applies, then Item(0, 0) will be one back to the left and one back up )

Alan Elston


2021-10-26 04:27:50

Rick Rothstein

Here is another way to select the first cell in the current row...

ActiveCell.EntireRow.Cells(1).Select

You can change the 1 to whatever column number you want. For example, if you wanted to select the cell in Column G (7th column) on the current row...

ActiveCell.EntireRow.Cells(7).Select


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.