Results 1 to 11 of 11
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Selection is too large (Excel2000)

    I am trying to copy a row of 8 formulas down a datablock of about 7000 rows.
    Excel gives a message 'Selection is too large'.

    So what are the limits?
    Can't see any reference help in Excel.

    I can copy the 8 formulas down one column at a time OK.
    Any comments gratefully received!

    zeddy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selection is too large (Excel2000)

    This is not a fixed limitation, but one depending on available memory. You may be able to paste everything in one go if you close all other applications (or after installing more memory <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>). Otherwise, fill down a few columns at a time, or less than 7000 rows at a time.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Selection is too large (Excel2000)

    Hans, I get this message under Widows2000 professional with 256MB- I didn't think it would have 'memory' issues.
    I don't have any other applicaions running.
    The Excel file I'm using is under 2MB.

    zeddy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selection is too large (Excel2000)

    It's not just the file size, the complexity of the formulas plays a role too. 8 columns by 7,000 rows means that 56,000 formulas have to be created and recalculated in one go!

    I'll refrain from making jokes about widows.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Selection is too large (Excel2000)

    Oops with the spelling.

    Just to continue..
    when I use
    =info("memavail") I always seem to get 1048576 (which is exactly 1 MB), even with a blank new workbook.
    Checking a couple of larger workbooks to hand
    =info("memused") returns upto 16MB for some files, with memavail still 1MB
    On the particular file in question, 'memused' is around 10MB - this includes the Excel overhead of around 8MB.

    So I'm puzzled as to why I'm getting the 'selection too large' message.
    Yes, with around 56K cells it's a lot of formulas, but even at say, 100 chars per formula (which it certainly isn't) that would only be around 5.6MB - certainly within available limits on 256MB PC I would have thought.

    This exercise is being handled through VBA - I wanted to automate the formula copy to match incoming imported data rows.

    zeddy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selection is too large (Excel2000)

    Well, I don't know how Excel works internally, but filling 56,000 cells with formulas must involve much more than the combined length of the formulas - Excel must keep track of all the dependencies between cells, recalculate everything according to these dependencies etc. But I have no idea how much memory that takes.

    Perhaps others have inside insights?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Selection is too large (Excel2000)

    To expand a little on what Hans wrote:
    This type of copy (I think) triggers LOTS and LOTS of recalcs! It might be better to turn on manual calc BEFORE the copy to alleviate the problem.

    The first cell gets copied and then a recalc is triggered, now the second one and another recalc, etc. and every one you add adds to the complexities that excel must keep track of and also continue to calc.

    Steve

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Selection is too large (Excel2000)

    Perhaps Zeddy can save the file as HTML and then re-save the HTML version as XLS, in case there is some corruption. Also, he could post the formula and VBA code, so we can see the degree of complexity and in case there is some error. I was also wondering vaguely if the formulas are set to Iterate, and that is overloading the system. In WIN 2000/XL 2000 on a 1.5 Ghz Machine with 256 Mb memory just yesterday I copied a simple datevalue formula from row 8 to row 65536 in three sheets simultaneously without problems.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Selection is too large (Excel2000)

    I saved to HTML and back again - no change.

    I have attached the relevant sheets extracted from the file and removed most of the datarows etc.
    So, the issue I was having on sheet named [FileB] in the attached workbook:
    I was trying to copy the range named [formulaRow2] to range c6:c6944 in one go.
    No joy - selection too large.
    FYI, I also have tried the same where the formulas within the range [formulaRow2] have been adjusted to refer to explicit ranges rather than whole columns on other two sheets. Same result - selection too large.

    OK, so you spotted I had some conditional formats in 2 cells within my formula source range.
    Well, I didn't think they would hurt but yes they do.
    If I take them out everthing works.
    So I'll apply them after the copy. Sheeesh.

    Thanks for everyones help.

    zeddy

    zeddy

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selection is too large (Excel2000)

    In my experience formatting is a far more voracious memory hog than formulas, looks as if conditional formatting combines the worst of both worlds. Successive Paste Specials, Values, Formatting, Formulas may be another solution to your problem.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Selection is too large (Excel2000)

    It also doesn't help that there are several MATCH's which are for exact finds and each one checks all 65,536 cells in the column!

    Defining a "real range" to search might be better and also improve the situation.

    Steve

Posting Permissions

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