Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Multi column memory issue Excel 2013

  1. #1
    New Member
    Join Date
    Aug 2014
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Multi column memory issue Excel 2013

    Hi,

    I have been asked to generate a spread sheet to contain a count of occurrences of text in Excel 2013 32 bit.

    The file I am working with is 3637 KB, when I open it it takes > 1.2 GB of memory, which means that I soon get out of memory errors.

    There are 7310 different text to search for which means that I have > 7363 columns and about 39000 rows.

    To create this I put the text I was searching for in row 2 then used COUNTIF to count for each text. To avoid Excel blowing up, I wrote VBA to fill down column by column, then copied the column to value. I then replaced the 0's with a blank to try to save space in the file.

    This file is a sub set of the data that I need to work on. All together I need to work with 13186 columns of data for the 39000 rows.

    There are no formulas in the file, everything is copied back to text. There is also no formatting on the cells, or anything that can explain why the memory use is so large.

    Excel 2013 is supposed to cope with 16,384 columns and 1,048,576 rows. As there are no formulas in my data, I would expect Excel to be able to handle this comfortably.

    I have tried the usual suspects such as removing Excel Add-ins.

    Does anyone have any suggestions on how I can get around the memory problem?

    Thanks in advance for your suggestions.

    Regards,

    Mark

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,370
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi column memory issue Excel 2013

    Do a test on a copy of your file and see if this helps:

    Delete styles in MS Excel | Microsoft Excel and Access Experts Blog

  3. #3
    Board Regular ranman256's Avatar
    Join Date
    Jun 2014
    Location
    Kentucky
    Posts
    1,774
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi column memory issue Excel 2013

    Excel 32 bit does NOT access all of the PC memory. bug. It is stuck in 3 Gig limit so you have no option. Mine will crash now and then.

  4. #4
    New Member
    Join Date
    Aug 2014
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi column memory issue Excel 2013

    Hi,

    I tried saving as a new file, but this makes no difference.

    Regards,

    Mark

  5. #5
    New Member
    Join Date
    Aug 2014
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi column memory issue Excel 2013

    I know that there is a memory limit.

    My problem is I have a file < 4 megabytes taking 1.2 gigabytes when opened.

    Quote Originally Posted by ranman256 View Post
    Excel 32 bit does NOT access all of the PC memory. bug. It is stuck in 3 Gig limit so you have no option. Mine will crash now and then.

  6. #6
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,370
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi column memory issue Excel 2013

    Quote Originally Posted by Mark Sapey View Post
    Hi,

    I tried saving as a new file, but this makes no difference.

    Regards,

    Mark
    Did you run the code in the page I linked to remove all custom styles?

  7. #7
    New Member
    Join Date
    Aug 2014
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi column memory issue Excel 2013

    Hi Mark,

    I am afraid that this makes no difference it is still taking 1.2 GB of memory.

    If I save as a xlsb file, it takes 2749kb.

    I don't see how a small file can take so much memory.

    Regards,

    Mark

  8. #8
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,370
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi column memory issue Excel 2013

    Quote Originally Posted by Mark Sapey View Post
    Hi,

    I tried saving as a new file, but this makes no difference.

    Regards,

    Mark
    Quote Originally Posted by Mark Sapey View Post
    Hi Mark,

    I am afraid that this makes no difference it is still taking 1.2 GB of memory.

    If I save as a xlsb file, it takes 2749kb.

    I don't see how a small file can take so much memory.

    Regards,

    Mark
    Apologies for being pedantic but you didn't answer the question. Did you run the code?

  9. #9
    New Member
    Join Date
    Aug 2014
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi column memory issue Excel 2013

    Sorry I did run your code.

  10. #10
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi column memory issue Excel 2013

    Hi there,

    "Excel 2013 is supposed to cope with 16,384 columns and 1,048,576 rows." No, that's not true at all. While it may contain that many cells on the grid, by no means should that give anyone the idea that you can actually fill every cell with data. You have a very, very large spreadsheet. For just values, that file size is astronomical. I'd recommend either a better machine, or putting this in a database and analyzing there, otherwise you'll continue to run into the same problems.

    As mentioned, Excel 32-bit will not run as fast as Excel 64-bit. The file will continue taking up quite a bit of memory, because it's a large file, "which means that I have > 7363 columns and about 39000 rows." By my calculation that's over 287 million cells with data, quite large by anyone's estimation. On a typical large Excel file, you'll only see several hundred columns used, not several thousand. Just because the grid is sized the way it is, doesn't mean it's there to fill.
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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