cap Excel 2010 rows and columns in new spreadsheets

tushiroda

Board Regular
Joined
Mar 21, 2005
Messages
156
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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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.
 

tushiroda

Board Regular
Joined
Mar 21, 2005
Messages
156
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.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,068
Office Version
365, 2010
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:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,935
Office Version
2013
Platform
Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,090
Office Version
365
Platform
Windows
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:

tushiroda

Board Regular
Joined
Mar 21, 2005
Messages
156
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,747
Messages
5,482,614
Members
407,354
Latest member
Calvince

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top