Copying entire Column then inserting copied cells (column) takes 10-15 minutes to complete???

Copper Contributor

Hello. I have a spreadsheet with a column or two that I need to shuffle around. This column is largely empty from a data perspective. It does have a number of formulas and conditional formatting in place between rows 12 to 1500 but only approx. 18-19 rows with any kind of external data entered.

 

When I attempt to copy a column from top row to last row then "insert copied cells" in between two other columns, it can take from 0-15 minutes...you read that right folks. 10-15 minutes to complete that task. I have pulled up my Task Mgr. to watch what is happening from a hardware perspective. Before initiating this action my CPU is 8-10% and RAM sits at 650MB-700MB out of 16Gb of total ram and no meaningful programs open and actively affecting either category. Once I initiate the action I described, the CPU moves up a bit but not anywhere above 40%. The RAM usage typically goes as high as 6,500MB with the highest level at or around 10,500MB-11,600MB or around 88%-92% of available RAM. Once the task completes the RAM continues at this level for up to an additional 10-15 mins or longer to go back to the original 650MB-700MB range. Other far smaller sheets don't experience this issue and generally my computer runs cleanly. It started doing all this with this spreadsheet (probably file specific causes at play here) around 3 days ago for no apparent reason. No massive data input or additional formulas or conditional formatting added (but a small number of cells did have these attributes added during this past 3 days which is pretty much always the case as this sheet evolves). I have included a small capture of the file details but will wait to upload the entire workbook until someone lets me know they want to take a look at this issue and need the file to do so.

 

I do have backups of the current file but unfortunately not sequentially named so as to preserve the last known good working file. Just copies of the file with this problem...

 

I appreciate your assistance in advance!

 

Douglas

2 Replies

@DouglasWilsonCAgmail 

 

It sounds to me like the basic design--into which this whole procedure has been fitted--is what might need to be reexamined.

 

So before posting the actual workbook--that image doesn't really tell us much--why don't you describe the business context here? To the extent that you can do this without violating confidentiality or proprietary details, describe what the spreadsheet is all about in the first place. Such things as:

  • What kind of data are you working with?
  • What are the inputs?
  • What are the outputs?
  • What sort of processing of inputs is needed in order to produce the output?
    • (Presumably this is where that column manipulation fits.)

 

I appreciate you reaching out on this but it looks like the downloading of a recent WIN update cured the issue, at least for now. There had been a WIN update sitting in wait to install but my system never notified me as it usually does. This does make sense for my experience with more recent versions of Windows. Without fail, when I notice my system is really underperforming an update to be installed is the culprit. This was both no different and also different. Different because the update wouldn't install. It failed to install in 4 consecutive attempts. I called Windows support directly and after additional unsuccessful attempts at making this update I was sent to Level 2. They had methods to force the update to install. What they did worked but left out one additional .net update which I installed without issue. After reboot completed I booted Excel and the suspect workbook and it performed normally. And, that is my tale!

Again, thanks very much for reaching out!

Douglas