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.

Selecting a Cell in the Current Row

by Allen Wyatt
(last updated June 3, 2015)

22

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.

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

Non-printing Page Borders

With your page border in place, you might be surprised if you don't see one side of the border (or all sides) print out with ...

Discover More

Exactly Positioning Text

If you need to control exactly where text will appear on the page or relative to other text, you need to know about the ...

Discover More

Automatically Referencing Info Entered in a Table

Tables are a great way to organize information in a document. At some point you may want a cell in a table to contain the ...

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 (menu)

Putting an X in a Clicked Cell

Need to click on a cell and have it replaced with an "X"? Macros make it easy to do, as illustrated in this tip.

Discover More

Making Common Functions Available to Others

When you use macros to create functions, you might want to share those functions with others—particularly if they ...

Discover More

Running Macros in the Background

Want to run a macro in Excel, but not sure if doing so will tie up your computer? Here's how macro processing really happens.

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}] 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 6 - 0?

2017-09-30 13:25:53

Willy Vanhaelen

@Bryan Turner
This macro will do the job automatically:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 11 Then Target.Offset(0, 2) = Target.Value
End Sub

The macro must be copied to the code page of the sheet you want it to work in. To get there, right click the sheet's tab and select View Code.


2017-09-30 07:16:13

Bryan Turner

Hello

I want to create a macro that will enter the results of calculations into a specific cell in the same row as the cell that I change. I.E.

Change K3 - run macro that copies the result from another cell into M3; or

Change K20 - copies result to M20


2017-06-15 04:35:03

Alan Elston

Hi needhelp,
I do not understand exactly what it is you are asking.
Maybe this will give you a start to get the general idea of how to easily in VBA do stuff to cells in a selection.
The following code will write the address in all cells in a selection

Sub DoStuffWithMultipleSelectedCells()
Dim ACell As Range
For Each ACell In Selection
Let ACell.Value = "My Address is " & ACell.Address(Rowabsolute:=False, columnabsolute:=False, ReferenceStyle:=xlA1, external:=False, relativeto:=A1)
Next ACell
End Sub

Alan

P.s. You probably know how you select a multi cell area selection of contiguous adjacent cells:
_You put the mouse cursor on any corner cell of the selection you want , then hold the left mouse key down and drag the mouse so that the area you want is selected.
_ You can select multiple cells that are not contiguous as follows: Click on one of the cells you want selected. Or click on one of the areas of contiguous cells you want selected. Press and hold down the Ctrl key on the keyboard. Select all the other single cells or areas of contiguous cells you want selected without releasing the Ctrl key until you have finished selecting the entire selection that you want selected.


2017-06-14 15:57:28

needhelp

I have code that will parse a cell that I select, then I run the code. I want to be able to select multiple cells, then run the code on each cell in my selection. I want to do this so I don't have to select each cell individually to run the macro. Code below, thank you for your help:

Sub Reformat()
'
' Reformat from DELIMITED field to FIXED fields.
'

Range(Cells(Selection.Row, 1).Address).Select
Selection.TextToColumns Destination:=Range(Cells(Selection.Row, 1).Address), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 2), Array(2, 2), Array(3, 1)), TrailingMinusNumbers:=True
Range(Cells(Selection.Row, 2).Address).Select
Selection.Cut
Range(Cells(Selection.Row, 3).Address).Select
ActiveSheet.Paste
Range(Cells(Selection.Row, 1).Address).Select
Selection.Copy
Range(Cells(Selection.Row, 2).Address).Select
ActiveSheet.Paste

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

If Right(Range(Cells(Selection.Row, 3).Address), 3) = "SFF" Then
Range(Cells(Selection.Row, 3).Address) = Left(Range(Cells(Selection.Row, 3).Address), Len(Range(Cells(Selection.Row, 3).Address)) - 4)
End If

End Sub


2016-10-08 20:40:02

Jeff

And to apply =(1- cellvalue) to a range of cells

Thanks for any help


2016-10-08 20:03:02

Jeff

I am trying to have a macro
=(1-valueincell)

e.g. =(1-0.12%)


2016-09-22 12:46:50

Kevin M.

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


2016-07-28 03:33:29

Bhagyashree

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


2016-04-25 08:34:00

jimmy

if i have a large data. how to sort according with different row?? how to use erow on colomn 4


2016-03-11 15:30:05

ken dupont

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


2015-12-17 09:56:43

Music

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.


2015-03-08 06:09:14

Michael (Micky) Avidan

@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


2015-03-07 12:30:30

JMJ

Sorry Micky, I didn't see you answer before posting :-[


2015-03-07 12:28:58

JMJ

@Ivor Shaer:

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

Hope this helps


2015-03-07 05:51:25

Michael (Micky) Avidan

@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


2015-03-06 21:04:08

Ivor Shaer

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


2013-08-19 20:44:38

Adolfo Vazquez

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.


2013-07-22 13:55:21

joseph

How I Clear Some cello content current row vba


2013-03-11 20:30:09

Aadesh

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.


2012-11-02 10:51:52

VideoFlyer

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


2012-02-18 09:25:40

jmj

Another simple solution is to use a named cell (or field) and then you can use:
[name].select
Neat, no?


2012-02-14 08:14:32

Dave Roberts

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.


This Site

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.

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.