1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macro which Selects Last cell in a Row?

Discussion in 'Business Applications' started by NortonPkg, Jan 17, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. NortonPkg

    NortonPkg Thread Starter

    Joined:
    Jan 17, 2008
    Messages:
    9
    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. :confused:
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    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
     
  3. NortonPkg

    NortonPkg Thread Starter

    Joined:
    Jan 17, 2008
    Messages:
    9
    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!
     
  4. NortonPkg

    NortonPkg Thread Starter

    Joined:
    Jan 17, 2008
    Messages:
    9
    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?
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    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)>
     
  6. NortonPkg

    NortonPkg Thread Starter

    Joined:
    Jan 17, 2008
    Messages:
    9
    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!
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    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. :)
     
  8. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/672947