Huge performance difference when filling a workbook with VBA
Posted on 2010-09-01
I have developed a small VBA application that reads a text file which contains values for cells in Excel.
The problem is that i see huge perfomance differences:
- Filling a blank workbook takes about 3 Minutes (12 columns, 40000 rows = 480000 cells filled)
- But if iI use a filled worbook, rename the filled sheet, insert a new sheet with the filling target sheet name and start my filling program again using the modified workbook as target, then the filling completes in about 5 seconds.
Additionally i found out that the time for filling of a blank workbook goes down to ~45sec (~25%) when reducing the rows amout to 20000 (50%)
So filling of the last cell takes much longer than filling of the first cell.
Why is it faster to fill a huge Workbook than to fill a blank one, a why is filling of the last cell so much more expensive?
Is there a way to work around this behavior?
Environment: Win XP Pro, Excel 2003 Pro