# Excel Macro which Selects Last cell in a Row?



## NortonPkg (Jan 17, 2008)

Hi there,
I'm attempting to make a macro in an excel workbook which will find the first unused cell in a given row and then select it, so that I can have the user input whatever information it is that they wish to input. I've got that second part of the problem figured out, but I cannot seem to figure out good code which will select the last cell in the row. I found on another webpage this code:
LastRow = Range("A6000").End(xlUp).Row
which apparently uses the End function to find the last cell in a given column and then names this cell "Last Row". This is basically the opposite of what I am trying to do, so I attempted to reverse most of what was going on in this line of the code:
LastColumn = Range("BJ1").End(xlLeft).Column
However, this code, and any variation of it which combines End and Column always ends up encountering a bug in the code and failing when used. Could anyone lend me some support here? I'm really hurting for it.


----------



## Zack Barresse (Jul 25, 2004)

Hi there, welcome to the board!

You don't need to select anything pretty much. The biggest reasons people do this is for 1) effect, 2) leftover code from the macro recorder, 3) working with shapes.

For finding the last row in a range, take a look at Bob Phillip's white paper on finding the last value in a range, from which you can extract the row of course...

http://www.xldynamic.com/source/xld.LastValue.html

I'm not sure why _LastColumn = Range("BJ1").End(xlLeft).Column_ would fail, we'd have to see all of your code, an example of your data, know what line errored out and the error message you got. From what you have here, it could be anything. That specific line looks good though. As you'll find in Bob's white paper [link], there is no need to hard code a value/address. Personally, I prefer the find method.

For functions of this, check out the work of MWE in the VBAX Knowledge Base..

First or last row:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=417

First or last column:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=418

HTH


----------



## NortonPkg (Jan 17, 2008)

Firefytr...I really appreciate the quick and extremely helpful response. I haven't finished setting the macro up yet, but when I do, I'm sure it will work! Thanks a bunch!


----------



## NortonPkg (Jan 17, 2008)

Well, I know that I'm being very dense, but the fact is that I'm an excel macro newbie, in over my head. All of those solutions will display either the value in the last used column or in the case of your second set of links, will display the number of the column (ie. Column 11=K because K is the eleventh column out). This is very close to what I want, but I really need some formula which will actually *SELECT* the next cell after this last used column cell. Could anyone suggest some code which will accomplish this?


----------



## slurpee55 (Oct 20, 2004)

Just so I can understand this a bit better (and to be honest, so the real brains here can then answer you) you say you want a macro to select the first empty cell in a row - are we assuming that, if you put the cursor in row 18 and then run the macro, you want it to go to the first empty cell (after all the others have been checked) or do you want it to enter "Last Column" in that cell? Additionally, do you want all the "Last Column" cells to be listed, say on another worksheet?
Also, will there be other empty cells in that row before the end of the data? (This could completely screw up some attempts and will have to be accounted for)>


----------



## NortonPkg (Jan 17, 2008)

Just so everyone knows, the code I finally found which fixed by problem was very simple:
Range("IV1").End(xlToLeft).Select
Selects the cell in the last column in a row of cells. Yippee!


----------



## Zack Barresse (Jul 25, 2004)

Yes, of course, but like I said, you have very little _need_ to select anything. If you posted a sample of your data and described what you were doing, we could probably whip up some more efficient code for you. Whatever's clever though.


----------

