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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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:
 
Upvote 0
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:
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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. ;)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top