Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 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.
Written by Allen Wyatt (last updated October 26, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 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:
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:
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 Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Selecting a Cell in the Current Row.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
When you copy information from one worksheet to another using a macro, you might not get exactly what you want. This tip ...
Discover MoreWhen you have a macro that processes a huge amount of data, it can seem like it takes forever to finish up. These ...
Discover MoreMacros depend on the use of variables to do their work. This tip examines how variables are declared in a macro, using ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-10-29 06:03:17
@ 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
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
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
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 © 2024 Sharon Parq Associates, Inc.
Comments