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 VBA - setting last used cell

Discussion in 'Business Applications' started by Gram123, Jun 1, 2009.

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

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Hi,
    In Excel, I have a macro which, when ran, resets the last used cell, so that when you press CTRL + End, the last cell (bottom-right) that contains data is selected.

    Here's the code:

    Sub Reset_Range()
    ActiveSheet.UsedRange
    End Sub


    However, sometimes this doesn't work. I run the macro and the 'active cell' is an empty one way off away from the data. Presumably this is because something's been in those now-empty cells previously. Maybe the have cell formats or something, and it stops the macro from working correctly.

    E.g. I have a file with data in cells A1:K6154. Before, and after, running the macro, pressing CTRL + End takes you to cell K8621.
    If I delete the rows 6155 to 8621, re-run the macro and press CRL + End, it still takes me to K8621.
    If I delete the cells A6155 to K8621, re-run the macro and press CRL + End, it works correctly and takes me to K6154.

    Can the code be modified so that for cells to be within the "used range", they must have an actual value in them?

    Cheers.
     
  2. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    If your range is in a block, and always set to the leftmost column (ie. Column A) then perhaps you would consider using this to select the rightmost in the last row of your range...



    Cells(Application.Rows.Count, 1).End(xlUp).End(xlToRight).Select
     
  3. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    It's not just to find the last used cell, though, it's also about economising.
    The macro should stop all those excess cells from acting like they're within the "UsedRange".

    Some users have many worksheets in a workbook, and due to data changing over time, have huge areas of what appear to "blank" (but are apparently "used") cells bloating the file size.
    I saw one file recently that was 13Mb prior to resetting the UsedRange, and 2Mb afterwards.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Hi Gram. :)

    I read that Application.ActiveSheet.UsedRange "always works".

    HTH
     
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/831585

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice