Running this Subroutine Causes my File Size to Increase Dramatically

**faithdm** · 03-08-2021, 08:54 PM

I'm running Excel for Mac version 16.46. I have a .XLSM workbook with three worksheets, a small amount of data (approx 100 rows of data in each worksheet), and some VBA code that does some calculations when I enter new data rows. I have one subroutine that protects all but one row in the data entry worksheet to ensure that the user only enters data in one row at a time. The workbook file size is about 200KB, but when I run this subroutine one or more times, the file size increases to 102MB. I assume this problem is related to the fact that I'm protecting a huge block of cells (rows 1 - current row, and current row+1 - 65536). Is there a cleaner way of doing this? The code is pasted below. Thanks in advance for any help you can provide.

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

**6StringJazzer** · 03-08-2021, 09:17 PM

You are forcing the size of this sheet to be over 39 million cells even though most of them are probably not being used. That takes up space. I can't tell what you're up to in setting cells to locked, when cells are locked by default. Also, why are you setting colorindex to 0 for the entire worksheet?

Can you explain in a little more detail what you are trying to achieve so we can consider alternatives?

**faithdm** · 03-09-2021, 09:30 PM

Thanks for the feedback 6String. Here's what I'm trying to accomplish. On this worksheet I only want the user to be able to enter data on the first open line at the bottom of the table. So, if for example the last line of data in the table is line 112, I only want the user to be able to enter data on line 113. I also want to highlight line 113 with a green color. The reason I was setting the colorindex for the whole sheet to 0 was to clear the green color highlight from the previous line of data entry. That was just me being lazy, using brute force rather than determining the previous line number and clearing just that. I hope this makes sense.