Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Dynamic ranges - performance comparison

    I am converting some fixed ranges into dynamic ranges in a rather large workbook.

    In performance terms, is it better to use an OFFSET formula for the dynamic range definition or to use an INDIRECT formula ?

    Or, as they are both volatile functions perhaps it makes no difference ?

    Maybe I should have asked whether there's a way of defining a dynamic range without using a volatile function ?

    Thanks
    Last edited by MartinM; 2016-04-03 at 04:47.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I think I may have solved this, using an INDEX formula:

    I started with

    =SheetX!A23:A6000

    and now have

    =SheetX!A23:INDEX(SheetX!A:A,LastRow)

    . . . where LastRow contains the row number of the last populated row (6,000), already calculated elsewhere.

    Is this the fastest construction in terms of calculation ?
    Last edited by MartinM; 2016-04-03 at 05:01.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2011
    Posts
    112
    Thanks
    35
    Thanked 3 Times in 3 Posts
    Last edited by jrb; 2016-04-03 at 09:22. Reason: References

  4. The Following User Says Thank You to jrb For This Useful Post:

    MartinM (2016-04-03)

  5. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks for the information.

    I got the idea from your references of constructing a Workbook with thousands of examples and trying out the different methods.

    =SheetX!A23:INDEX(SheetX!A:A,LastRow) is indeed the fastest that I have found, by a good margin.

Posting Permissions

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