+ Reply to Thread
Results 1 to 4 of 4

Setting a range that ends where data ends?

  1. #1
    Registered User
    Join Date
    06-25-2013
    Location
    NC
    MS-Off Ver
    Excel 2007
    Posts
    13

    Setting a range that ends where data ends?

    I was curious about whether, where I make computations in my macro, there was a way to set the range so it included only the cells with data. The amount of rows would be changing every time that I had to run the macro, so simply defining the range as a1:a347 or something similiar wouldn't work, and things like a:a take a long time to run.


    (As of now, I have all of my formulas set for "Autofill" from row 1 to row 500 of whatever column it is needed in, however having it fill to the relevant row would be preferred)
    Last edited by Mgassma; 07-02-2013 at 09:32 AM. Reason: Still am a little confused, but now I know the proper place to go in order to find the information I need and learn it for myself. Thanks!

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Setting a range that ends where data ends?

    Hi Mgassma,

    You can use define name powered with Offset & counta functions.
    Do some research on this over internet and if stuck, upload a sample workbook and I'll be happy to create one name for you. Thanks.


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    06-25-2013
    Location
    NC
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Setting a range that ends where data ends?

    Thank you!

    As I added to your reputation, even though you didn't write me any code, I appreciate you sending me off in the right direction (perhaps moreso than if you wrote me a code, since I will actually learn this way, and know what I'm doing in the future).

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Setting a range that ends where data ends?

    Okay

    See the attached file where you'll find a define name "data". Press ctrl + F3 and click on the right side icon at formula entry bar.. this willl show you the current range. Now come out.. add more data and again check the range, you'll find the range is automatically expanded to accommodate newly entered data

    dynamic name.xlsx

    hope this helps.


    Regards,
    DILIPandey
    <click on below * if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1