GWC said:
Is is normal for the same process to take significantly
longer each time it is repeated
"Normal?" That cannot be answered in general. The answer is: it depends.
"Is it possible?" Yes. See the details below.
GWC said:
or is there something wrong with my computer?
The fact that the process takes 8 seconds(!) the first time suggests to me
that something is indeed "wrong" with your computer.
That is, one or more of the following conditions are probably true:
1. The computer is memory starved.
2. There is too little disk space available for virtual memory, and/or the
disk is very slow and fragmented.
3. The CPU is very slow.
4. You have software or an environment (e.g. noisy LAN) that is interrupting
the Excel process unduly. That software might be viruses; or it might be
legitimate software running in the background (e.g. Norton).
Also, you neglect to say what version of Excel and Windows you are using.
That could be a factor since some versions have known problems with memory
leaks.
GWC said:
My spreadsheet has 9 columns and 51,000 rows.
It contains no macros nor formulas. Here's what I'm doing:
I click on the "A" at the top of COL A.
In the FIND and REPLACE box, I type DUPL after 'Find What'
and, for Options, I click 'WITHIN SHEET", "SEARCH BY COLUMNS"
and "LOOK IN FORMULAS". I then click FIND ALL and it returns
588 cells.
I select the first cell listed and hold down the SHIFT key
and select the last cell listed. (That selects all 588 cells
listed). I then click INSERT and INSERT CELLS and SHIFT CELLS RIGHT.
The first time, the "shifting" process" take 8 seconds.
the second time takes 20 seconds
the third time takes 80 seconds.
the fouth time takes 140 seconds
the fifth time takes 200 seconds
the sixth time takes 6 minutes.
the seventh time takes 20 minutes.
As I noted above, 8 seconds seems unduly long for the first time.
Nevertheless, I also see an increasing run time when I follow your procedure
as I understand it. See the details below.
The reasons might be two-fold.
First, we expect the procedure to take an increasing amount of time somewhat
if only because the spreadsheet is growing. Theoretically, this might be
exacerbated by the fact that you are always inserting from the originally
selected cells, if my understanding is correct.
I say "theoretically" because in my experiments, that actually does not make
any significant difference(!). I think that demonstrates that the second
reason below is the dominant factor.
Second, we expect the amount of memory for the workbook grows as we insert
and shift right.
But in fact, it appears that the amount of memory grows inordinately, very
much more than it should. This is confirmed by saving the modified file,
closing Excel, then reopening the file. Again, see the details below.
Details....
I am using Excel 2010 with WinXP, both with fairly recent updates. My
computer is a lowly single-CPU single-core 2.13 GHz processor without
hyperthreading with 2 GB RAM.
I have forgotten my disk specs, and I'm too lazy to look them up. But the
disk is 50% full, old (read: probably relatively slow), and it has never
been defrag'd (read: data is probably widely dispersed).
I created a data-only workbook with 9 columns and 51,000 rows of numeric and
text data randomly distributed. Column A contains 588 cells the string
"DUPL" randomly distributed.
I use the macro below to do the FIND/select, then repeated insert with
right-shift.
When I open the Excel file with the initial data, the Excel process uses
about 77 MB of RAM and 43 MB of VM. (Note: WinXP seems to be "capricious"
about its VM usage. Sometimes I see as much as 64 MB of VM for the same
file.)
On my computer, the Find time and the insert-with-right-shift times are the
following:
Find: 0.594 sec
insert # 1: 0.172 sec
insert # 2: 0.250
insert # 3: 0.328
insert # 4: 0.422
insert # 5: 0.531
insert # 6: 0.688
insert # 7: 0.781
insert # 8: 0.969
insert # 9: 1.266
insert #10: 1.375
Obviously, your times might vary. Even my times vary from one trial
experiment to another.
But the key thing to note is: the insert times are indeed increasing each
time.
The reason seems clear when we look at memory usage. After running the
macro (performing 10 inserts), the Excel process uses about 528 MB of RAM
and 492 MB of VM.
Such differences in memory usage are likely to put a strain on the computer.
It might thrash the CPU caches. Concomitantly, it might cause more disk
traffic, which is significantly relatively slow.
In any case, my times are significantly faster than yours, starting with my
0.172 sec compared to your 8 sec.
One explanation could be that your computer has much less memory or slower
CPU and disk.
Another explanation is that your data is very different from my experimental
data.
If you would like me to make an apples-to-apples comparison, you can upload
an example Excel file (devoid of any private data) that demonstrates the
problem to a file-sharing website.
Then post the "shared", "public" or "view-only" link (aka URL;
http://...)
in a response here. The following is a list of some free file-sharing
websites; or use your own.
Box.Net:
http://www.box.net/files
Windows Live Skydrive:
http://skydrive.live.com
MediaFire:
http://www.mediafire.com
FileFactory:
http://www.filefactory.com
FileSavr:
http://www.filesavr.com
RapidShare:
http://www.rapidshare.com