Limit the size of the spreadsheet

Posted by Ian on November 17, 2001 12:36 AM

Hi, this is probably a simple task for you guys but it is driving me mad, I hope someone can help.
I have just transfered to Excel from Lotus 123, so am new to this, hence the question.
I have a spreadsheet, showing 365 days, in order to record holidays and sickness. When scolling down the spreadsheet, it goes beyond row 365, in fact right down to 65000 (ish). I have tried to delete the rows after 365, I've tried hiding them (big mistake).
My question is, how do I limit the spreadsheet to the number of rows I need?

Thanks, Ian

Posted by Dank on November 17, 2001 3:26 AM

Hello Ian,

I'm sure the most effective way of doing what you need would be to hide the rows-why is this a mistake? Alternatively, you could set the scroll area by doing this (this example assumes you're working with sheet1.)

Open the VB Editor (Alt+F11) and click View, Project Explorer. The Click View, Properties.

Click the relevant worksheet in the explorer (you may have to expand the tree). In the Properties window change the ScrollArea to $1:$365

This will prevent the user from scrolling past the 365th row.

Any good?

Regards,
Daniel.

Posted by Ian on November 18, 2001 12:10 AM

Hi Daniel,

this was just what I was after, many thanks. In reply to your question "why was this a mistake", hiding the rows just made the spreadsheet 66,000+ rows long and took forver to save and open.