+ Reply to Thread
Results 1 to 5 of 5

Variable Range definition to conserve memory.

  1. #1
    Registered User
    Join Date
    02-07-2013
    Location
    Carmarthen
    MS-Off Ver
    Excel 2010
    Posts
    4

    Thumbs up Variable Range definition to conserve memory.

    I am trying to cut down on the memory my Excel program uses.

    One problem is the use of whole column references , i need to use A$1:A$2000 rather than A:A UNLESS I have more than 2000 rows.

    In particular, I use a lot of Sumifs statements, particularly to summarise pivot tables. The number of rows displayed by the pivot table can vary wildly. Is there a way of setting the last row in a target range as a variable? It is relatively easy to calculate the last cell

    Lastrow=Match("Grand Total",A:A,0)

    Can I define ranges by reference to this variable? ie (A$1:A$(LASTROW)) and if so how? I could use VBA,but is there another way?
    Last edited by A40infobahn; 05-31-2021 at 12:07 PM.

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Variable Range definition to conserve memory.

    I think dynamic ranges is what you need. Check this https://www.excel-easy.com/examples/...med-range.html
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Forum Contributor
    Join Date
    05-18-2021
    Location
    London, Ontario
    MS-Off Ver
    365
    Posts
    115

    Re: Variable Range definition to conserve memory.

    maybe try using INDIRECT? You create a text inside of it and then it executes it as if it was code

    e.g. INDIRECT("A$1:A$" & {formula or method of returning the number of the row you want})

    Then you can put the whole INDIRECT() formula inside of your match function where the range would be

  4. #4
    Registered User
    Join Date
    02-07-2013
    Location
    Carmarthen
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Variable Range definition to conserve memory.

    Thank you Both, I think KOKOSEK has it. Will experiment with this over the next couple of jobs.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Variable Range definition to conserve memory.

    Can I define ranges by reference to this variable? ie (A$1:A$(LASTROW)) and if so how?
    Yes. Personally I prefer this method learned from Guru Tony Valko.

    Use approximate MATCH(25^25,$A:$A) for numbers and MATCH("zzzzz",$A:$A) for text to identify the last row.

    Then in Name Manger set the dynamic named ranges as =$A1:INDEX($A:$A,MATCH(25^25,$A:$A)) for numbers and =$A2:INDEX($A:$A,MATCH("zzzzz",$A:$A)) for text.
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Thrashing VBA Memory Leaks. Memory Loop Problems. Uncleared Memory in .Match Range Object
    By Doc.AElstein in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-10-2017, 05:17 PM
  2. Double row check & Range definition using variable
    By Rudo123 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-23-2015, 03:57 PM
  3. Variable Last Row Definition
    By NCF in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2013, 04:20 PM
  4. [SOLVED] Variable definition problem
    By Orestees in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-17-2012, 02:46 PM
  5. OleoObject name variable definition
    By mmf in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-05-2008, 05:09 PM
  6. Range Address Memory Variable Limitation
    By MSweetG222 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-12-2005, 02:40 PM
  7. Excel question with variable definition
    By Yong Kim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2005, 02:06 PM

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