Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel Macro which Selects Last cell in a Row?


(!)

NortonPkg's Avatar
NortonPkg NortonPkg is offline
Junior Member with 9 posts.
THREAD STARTER
 
Join Date: Jan 2008
17-Jan-2008, 05:30 PM #1
Excel Macro which Selects Last cell in a Row?
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's Avatar
Computer Specs
Member with 5,419 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
17-Jan-2008, 06:31 PM #2
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's Avatar
NortonPkg NortonPkg is offline
Junior Member with 9 posts.
THREAD STARTER
 
Join Date: Jan 2008
18-Jan-2008, 09:50 AM #3
Thanks Much
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's Avatar
NortonPkg NortonPkg is offline
Junior Member with 9 posts.
THREAD STARTER
 
Join Date: Jan 2008
18-Jan-2008, 11:03 AM #4
:(
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's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
18-Jan-2008, 11:37 AM #5
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's Avatar
NortonPkg NortonPkg is offline
Junior Member with 9 posts.
THREAD STARTER
 
Join Date: Jan 2008
18-Jan-2008, 12:28 PM #6
Figured it out
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's Avatar
Computer Specs
Member with 5,419 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
18-Jan-2008, 01:29 PM #7
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.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑