On Saturday, May 11, 2013 12:49:22 PM UTC-7, joeu2004 wrote:
"Howard" wrote:

On Saturday, May 11, 2013 9:59:40 AM UTC-7, joeu2004 wrote:

So even if we repeatedly delete, then add one worksheet,

eventually the add will fail. No to worry. IIRC, that

limit is around 10,000.

[....]

Makes me wonder what project would ever require a number

of sheets that massive. Say even 300 to 600...?



You misread. I did not say the limit is 10,000 worksheets. I said that we

"repeatedly delete, then add __one__ worksheet".



It is not uncommon to create temporary worksheets, then delete them. Some

operations are best performed by Excel instead of VBA. And it is not

uncommon do that repeatedly in an algorithm.



FYI, the limit is 10,915 in Excel 2010. And I can hit that limit in a loop

that requires less than 20 sec on my (ancient) computer.



Arguably, it might be better to create one temporary worksheet and use

Activesheet.UsedRange.Clear and perhaps some additional operations. But

that depends on why we are creating temporary worksheets in the first place.

Sometimes it is faster and more reliable to delete and add a new worksheet.



The more likely limitation that your algorithm might hit is due to memory.

As I mentioned before, Excel has an arbitrary limit that might be

significantly less than the computer memory.

So it is a code repetition issue rather than say... cataloging all the grains of sand on the beaches of California with a serial number, each in a separate cell.

Does that happen very often? I don't recall seeing posts looking for solutions around that issue. However, I mostly only read posts that I have some idea of what they are talking about. And that would be never-never land to this
low-hanging-fruit-only lurker.

Howard