cap Excel 2010 rows and columns in new spreadsheets
Results 1 to 8 of 8

Thread: cap Excel 2010 rows and columns in new spreadsheets

  1. #1
    Board Regular
    Join Date
    Mar 2005
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default cap Excel 2010 rows and columns in new spreadsheets

    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 2007

  2. #2
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,127
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: cap Excel 2010 rows and columns in new spreadsheets

    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.
    If you've posted a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel, THANK YOU.
    Google can find answers to nearly every question.


  3. #3
    Board Regular
    Join Date
    Mar 2005
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: cap Excel 2010 rows and columns in new spreadsheets

    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.
    ---------------------------------------
    Excel 2007

  4. #4
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    454
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: cap Excel 2010 rows and columns in new spreadsheets

    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 by kweaver; Jul 18th, 2019 at 07:04 PM.

  5. #5
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,503
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: cap Excel 2010 rows and columns in new spreadsheets

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,961
    Post Thanks / Like
    Mentioned
    80 Post(s)
    Tagged
    18 Thread(s)

    Default Re: cap Excel 2010 rows and columns in new spreadsheets

    Quote Originally Posted by tushiroda View Post
    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 by Peter_SSs; Jul 19th, 2019 at 05:34 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    Board Regular
    Join Date
    Mar 2005
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: cap Excel 2010 rows and columns in new spreadsheets

    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
    ---------------------------------------
    Excel 2007

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,961
    Post Thanks / Like
    Mentioned
    80 Post(s)
    Tagged
    18 Thread(s)

    Default Re: cap Excel 2010 rows and columns in new spreadsheets

    Quote Originally Posted by tushiroda View Post
    thank you all for taking the time to read through and reply to my little headache question!
    You are welcome. Hope it all works out for you.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •