Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Selecting a Cell in the Current Row

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

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.

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

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Kevin M.    22 Sep 2016, 12:46
If you just want a straight up copy of the cell you click on to appear in another cell (O6 for my example) just past in this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("O6").Value = ActiveCell
End Sub
Bhagyashree    28 Jul 2016, 03:33
How do i select and paste only the first line of a content in a cell .. Example.. the name line from the entire address .. Or jus the telpehone number as the last line from the address. in Excel
jimmy     25 Apr 2016, 08:34
if i have a large data. how to sort according with different row?? how to use erow on colomn 4
ken dupont    11 Mar 2016, 15:30
I need to copy cells A1, B1, C1,D1 to a different sheet F5,G5, H5, I5 . This is easy but Range(A1:D1) changes with new info. If I copy using a macro i need the macro to move old data down 1 row and paste special value into the Range(F6:I6). The Macro now places the new data into Range(A1:D1) moving it to Range(F5:I5). Here comes the problem. I now need to move the first 2 data sets down 1 row so new transferred data wont over write the top row
Music    17 Dec 2015, 09:56
Is there an easy way to get a cell in the current column?
As you know Activecell.column gets you a number and not the letter.
Michael (Micky) Avidan    08 Mar 2015, 06:09
@JMJ,
NP - but please note that this request was to refer(!) to that cell (meaning, to know it's address and probably not for selecting it).
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
JMJ    07 Mar 2015, 12:30
Sorry Micky, I didn't see you answer before posting :-[
JMJ    07 Mar 2015, 12:28
@Ivor Shaer:

Something as
Selection.Offset(0, 2).End(xlUp).Select
should do the trick.

Hope this helps
Michael (Micky) Avidan    07 Mar 2015, 05:51
@Ivor,
In order to return the expected cell's address - try:
MsgBox Selection.Offset(0, 2).End(xlUp).Address
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
Ivor Shaer    06 Mar 2015, 21:04
I need to reference, not select, the first used cell above a specified cell as follows
"First used cell above(Selection.offset(0,2)

Is this possible?

Thanks.
Ivor
Adolfo Vazquez    19 Aug 2013, 20:44
Mr. Wyatt, your solution to get the first cell in a row starting from any of same row was an issue I had tried to solve for long time without success. Till now with you. Thanks a lot.
joseph    22 Jul 2013, 13:55
How I Clear Some cello content current row vba
Aadesh    11 Mar 2013, 20:30
Hello,
   I am trying to get to the first row of the coloumn I am in, I tried modyfying the formual you have Cells(Application.ActiveCell.Row, 1).Select

but was not able to get it to work.
What I eventually want to get to is this, for any cell in the column when I run the macro it should give me product of first cell in the coloumn and third cell in the column, something like this (=C3*$C$1). Also the first cell should be fixed.
VideoFlyer    02 Nov 2012, 10:51
Using the same methodology, a formula can also include "ActiveCell.Row". Once in the cell you want to put the formula, be it manually, or with a macro, a simple format would be:

ActiveCell.Formula = "=A" & ActiveCell.Row
jmj    18 Feb 2012, 09:25
Another simple solution is to use a named cell (or field) and then you can use:
[name].select
Neat, no?
Dave Roberts    14 Feb 2012, 08:14
I'm sorry for the question versus a comment. You use activecell.row with range and application.activecell.row with cells. I tried with and without application. Does it make a difference? Thanks.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.