Multi column memory issue Excel 2013

Mark Sapey

New Member
Joined
Aug 21, 2014
Messages
6
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,916
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. :mad:
 

Mark Sapey

New Member
Joined
Aug 21, 2014
Messages
6

ADVERTISEMENT

I know that there is a memory limit.

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

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. :mad:
 

Mark Sapey

New Member
Joined
Aug 21, 2014
Messages
6

ADVERTISEMENT

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
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,377
Hi,

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

Regards,

Mark

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?
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,554
Messages
5,548,719
Members
410,867
Latest member
Dhanas
Top