Excel File Size Growth

[rodweston]

I am using Excel 2003. I have developed a workbook that is in shared use by at least two users 24x7. The workbook has eight forms, nine modules, and vba programming on each worksheet. There are two worksheets that rarely change and have little data. The other worksheets, one for each month, have headers on rows 1-4. Data is added at least every 30 minutes. When I set up the workbook for sharing, I instruct it to not save changes. In the course of the month, the worksheet will encompass about 1200 to 2000 rows and 40 columns. The data for each cell is simple and short, typically around six to ten characters with a maximum of 15.

I have a template for this workbook that is 812 kb. When I move the data into a new copy of the template, the size typically runs around 2MB. From that point, the size grows slowly but regularly. Within two weeks the size will reach over 20MB. If I load the data into a new copy of the template, it shrinks to no more than 4MB. As the workbook approaches 20MB, it occasionally acts erratically. After copying the data into the new copy of the template, it works normally again.

I update (copy the data to a fresh copy of the template) every month after copying the current copy to an archive and deleting the earliest month's worksheet, but I am having to update every two weeks because of this issue. Any ideas on what could be causing it? I've read all the related postings and tried what I found there to no avail.

Thank you.
Rod Weston

 

[teylyn]

Hi Rod,

check for the last active cell in the worksheets by pressing "End" and "Home". If this takes you to a cell far below and beyond the data entry range, this is where the problem may lie. Maybe some of your macros access cells outside of the active range and thus increase the active range each time the macro is run.

I've had that happen once and the problem went away after I made sure only the active part of the worksheet was handled by macros.

HTH

Teylyn

 

[rodweston]

Thanks. I'll check that immediately. I often go to the last entry on a worksheet using "lngLastRowNbr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row" and I try to be very precise in defining my ranges, but I do know that the last cell routinely ends up several thousands of rows below the last used cell. I've never been able to track down how or when this happens, but within twelve hours of updating to the latest version of the template, it will be there again. So I'll start looking at all my range definitions and see if I can find the culprit. Thanks again.

 

[rodweston]

I believe I've found it. In a simple little formatting routine, I had somehow used an ampersand in the place of a plus sign when defining my range. So instead of formatting the last row plus one more, I was formatting that row plus several thousand more! Thank you again for your assistance.

Rod Weston

 

[teylyn]

"Glad to be of service" - The Hitchhiker's Guide to the Galaxy

)

Teylyn