cap Excel 2010 rows and columns in new spreadsheets

tushiroda

Board Regular
Joined
Mar 21, 2005
Messages
159
Hi all,

I have a group of basic users who somehow always manage to max out the rows and columns in their spreadsheets. This of course increases the file size alot but also slows the system down as well as it eats up memory.

Is there an excel setting that will cap the row count and/or the column count? VBA or hiding rows would not work as I would have to be there every time they create a new file to work on. Also would prefer to maintain excel functionality and formatting capabilities to the extent possible. I have looked around in the forum and via google and have not been able to find a solution.

thank you in advance,
T
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Just guessing. If the file save option (ALT-T-O then S) is set to save files in Excel 2003 format (that is, *.xls) does that limit the worksheet to 65,536 rows? And 256 columns.
 
Upvote 0
hi Fazza,

I just gave it a try to save as .xls and that did not limit the rows to 65,536 in excel 2010.
 
Upvote 0
One thought is to right click on the sheet tab and View Code.
Then, F4 shows the Properties (if not displayed).
Scroll down to "ScrollArea" and enter something like: $A$1:$Z$100

If they're novice users, they won't know how to get into the VBA editor to change this.

I guess you could then have only 1 sheet in the workbook and protect the workbook with a password.

Or repeat this process for, maybe, only 3 sheets in the workbook.
 
Last edited:
Upvote 0
you could also make the excess rows and columns "disappear"
select the last row +1 and then press CTRL + down arrow, then Right click and Hide
THEN
select the last column +1 and then press CTRL + right arrow, then Right click and Hide
 
Upvote 0
hi Fazza,

I just gave it a try to save as .xls and that did not limit the rows to 65,536 in excel 2010.
I think you will find that when the file has been closed and then opened again, the rows & columns will be limited.
The issue will then be if they have already populated cells outside the Excel 2003 range, they will get warning messages when they do try to close the sheet. You would also have to rely on them saving in that format when they create a new workbook.

An option might be to have a Book1.xls (with the limited rows/columns) be the file that automatically opens when they start Excel. See here for additional info about that
 
Last edited:
Upvote 0
hi Michael and kweaver,
I am aware of the vb and hide options, but the issue is that I do not create the files for their use and the volume of files is high so I am hoping to not have to manually make these adjustments constantly.

Peter is correct in that once I closed the file it does get limited so I think the easiest solution would be to have them just use xls files and will look to implement a more restrictive area on start if it is still a problem. I can set them up to use .xls by default fairly quickly. The files that already exist will generate some errors, but those entries outside the Excel 2003 range are accidents/errors as there is no way they are using those intentionally with the small sets of data that they are working with so will just tell them to ignore those messages and convert.

the computers should be fine with an occasional 65k by 256 sheet I think so I will see how that goes.

thank you all for taking the time to read through and reply to my little headache question!

best,
T
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top