+ Reply to Thread
Results 1 to 4 of 4

Is there a way to limit the available amount of rows and columns?

  1. #1
    Registered User
    Join Date
    04-25-2015
    Location
    Ontario, Canada
    MS-Off Ver
    2016
    Posts
    72

    Is there a way to limit the available amount of rows and columns?

    My team has a 2 page document they use
    • Book 1 - external, sent to the customer, no pricing or anthing linked.
    • Book 2 - Internal version, linked to pricing, not sent to customer.

    Essentially, I would like them to be able to hit the "select all" in the top left corner, copy the customer version, and then paste all into the internal version. However, the internal version has other information, in areas that are blank on the customer version. I do not want these overwritten with blank cells.

    Is it possible to limit the customer version's rows and columns, so when they select all, I can determine the range of "all". That way they will only paste the range I want.

    If not, I will just teach them all about carefully copy and pasting the range but I'd rather keep it simple.
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: Is there a way to limit the available amount of rows and columns?

    Limit as you are asking is not possible in the strict sense of limiting.
    You would have to either write a macro that they can run to import the data from the customer file and let the macro handle where it goes.
    You can also protect the cells the may not be overwritten but then you'll get complaints that the user gets an error message.

    Select all is not a good option since select all will select all the 1048576 rows X 16384 columns that is 17,179,869,184 cells
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Is there a way to limit the available amount of rows and columns?

    Quote Originally Posted by Keebellah View Post
    You can also protect the cells the may not be overwritten but then you'll get complaints that the user gets an error message.
    isn't that the truth.

    Quote Originally Posted by Keebellah View Post
    Select all is not a good option since select all will select all the 1048576 rows X 16384 columns that is 17,179,869,184 cells
    [/quote]
    unless ""select all"" can be mirrored by text on a button to fool the users into thinking that the purposes are the same.

  4. #4
    Registered User
    Join Date
    09-10-2015
    Location
    London
    MS-Off Ver
    365 at work, 2016 at home
    Posts
    79

    Re: Is there a way to limit the available amount of rows and columns?

    There's already an element of function built into excel, when I hit ctrl-A, it selects all of the area I'm currently working with. If I repeat ctrl-A it THEN selects the entire sheet.

    If that doesn't do it, hiding ALL the rows and cols beyond the area you want copied might do it.

    If your last used col is H, highlight I, hit ctrl-shift-rightarrow, right click the col labels, select hide.
    The space beyond the end of your working area will be background-grey and unusable.
    Repeat the process for your unused rows - highlight the first one you don't use, ctrl-shift-downarrow, right click the row header labels, hide.
    ctrl-A should now only select the area visible.

    I've given this a little test run with a VERY simple spreadsheet, and it works. Something more complex might misbehave, so do test with your own workbook before rolling out to your team.
    Any rows/cols hidden within your working area - validations lists, working cells for complex formulae etc will copy along with, but if you're ctrl-A copying you've probably already worked around this - if not, move them to a hidden sheet that's for these ONLY.
    Last edited by cheesemeister; 11-27-2020 at 11:43 AM. Reason: spg

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Multiple rows to columns with different amount of cells
    By PainFoinmr in forum Excel General
    Replies: 1
    Last Post: 05-22-2020, 11:18 AM
  2. Replies: 4
    Last Post: 12-15-2015, 08:59 PM
  3. Limit amount of entries/rows in listbox??
    By ciapul12 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-20-2014, 11:59 AM
  4. Replies: 5
    Last Post: 11-11-2013, 04:31 AM
  5. Merge Duplicate Rows unique values into single rows for an infinite amount of columns/rows
    By aimeecrystalaid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-21-2013, 08:43 PM
  6. Limit scope of Rows(x).Copy to a specfic # of columns
    By PoggiPJ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2013, 01:15 PM
  7. How do I limit number of rows and columns on a spreadsheet
    By Valser in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 02-12-2005, 07:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1