Why does my computers lock up when copy and paste

[Jack-P-Winner]

I am curious, both my computers are locking up for about 4 to 5 minutes when I copy and paste columns of conditional formatting. I have plenty of memory, core 7 and a solid state drive with ONLY excel on it. I know my files are large but this one is rather small compared to what I normally run. As a matter of fact its less then 7000 kbs. Is there anything I could look at or run (software wise) to help fix this?

It does NOT say its calculating

Thanks !!!!

 

[Jack-P-Winner]

As I add different conditional formatting to different sheets I go through and update all the columns which there are 18 columns per sheet for a total of 4 sheets in each book which equals 72 columns X 51 rows per column with CF. What's happening is when I a copy and paste my computer's are freezing up. They have SSHD dedicated to excel alone using a 7 core 16 GIGS memory so I know its not an issue pushing the data



=SUMPRODUCT((J18:M18<>1)*(J17:M17=1),J18:M18)

=IF(C18=C19,J19+1,1) (this is in 4 columns X 72)

=ISODD(IFERROR(INDEX($C18:$F18,MATCH($B18,$J18:$M18,0)),1))

So it is getting obvious that I am doing a lot of calculating that I was unaware of. I am not sure how to turn this into a Macro. When I update my conditional formatting in each column I then copy and paste them to all the other columns in each book (18 columns per sheet for a total of 4 sheets in each book which equals 72 columns X 51 rows per column) x 5 books I have attached 1 of the sheets if anyone can help with a Macro

 

[Marc L]

Hi,

all that RAM for nothing if you are under a 32 bits Excel version !
Uses a maximum of 1Gb or 2Gb RAM …

Try with a small block. If success, try to upsize the block
until you reach the limit …

When it crashes, hard close the program and maybe reboot the computer.
​

 

[bobhc]

It is not your computer but Excel, if you just select a whole column instead of the rows/cells actually need then Excel will reserve memory for ALL the columns when you apply any CF excel reserves memory for it and where it is in the sheet, when you copy and paste Excel reserves more memory in the book for the new CF but the original is still in memory, the working of you book will soon get sluggish.

I have looked at some of your books and to say you love your CF would be an understatement.

 

[Jack-P-Winner]

Hi,

all that RAM for nothing if you are under a 32 bits Excel version !
Uses a maximum of 1Gb or 2Gb RAM …

Try with a small block. If success, try to upsize the block
until you reach the limit …

When it crashes, hard close the program and maybe reboot the computer.
​

I use 64 bit on both computers. One has windows 7 and the other 8.1

 

[Jack-P-Winner]

It is not your computer but Excel, if you just select a whole column instead of the rows/cells actually need then Excel will reserve memory for ALL the columns when you apply any CF excel reserves memory for it and where it is in the sheet, when you copy and paste Excel reserves more memory in the book for the new CF but the original is still in memory, the working of you book will soon get sluggish.

I have looked at some of your books and to say you love your CF would be an understatement.

Thanks Bob, I will try that. I am also in the process of converting it all to a Macro. Being new with Macros (but learning fast) it will take me some time

Yes I do love my CF BUT it sure makes things jump out at me that I would not normally see (-:

 

[Jack-P-Winner]

It is not your computer but Excel, if you just select a whole column instead of the rows/cells actually need then Excel will reserve memory for ALL the columns when you apply any CF excel reserves memory for it and where it is in the sheet, when you copy and paste Excel reserves more memory in the book for the new CF but the original is still in memory, the working of you book will soon get sluggish.

I have looked at some of your books and to say you love your CF would be an understatement.

Bob, do you think its my Conditional formatting or calculations causing it to freeze up? I am going to try and get some help writing a macro for the calculations but wanted your professional opinion.

Here is the thread:

http://chandoo.org/forum/threads/i-want-to-convert-my-calculations-into-a-macro.21059/

 

[Marc L]

Just try by block 'cause some Excel functions (as in VBA)
does not work with more than 65 536 rows !

Yes, conditional formatting wastes ressources …
The more CF and formulas, the more Excel hangs.

You can start your code by desactivating calculation and screen updating.

Edit : Excel' RAM limit : 2003 : 1 Gb

2007 and above in 32 bits : 2 Gb
2010 and above in 64 bits : 8 Tb​

VBA RAM limit :

x32 : 500 Mb
x64 : 4Gb …​

Source : Excel Memory Limits​

 

[bobhc]

As Marc L has said

Yes, conditional formatting wastes ressources …
The more CF and formulas, the more Excel hangs.

which more or less goes hand in hand with my comment, as you have show and said, you like CF, I mean you really like CF a lot, a look at some of the work books you have upload instils a desire to make an appointment with an Optometrist......

Don't get me wrong, I use CF, but limited, as most of my work is viewed by others. CF can help in the right place, to make a point or highlight certain data, too much and it distracts, big style, from the data being viewed and people soon stop viewing as it is hard work to see past the CF and see a clear picture of the more important part....the data.


.