Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Limiting the range of cells in a worksheet

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Southampton UK
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How can I limit the number of cells in my spreadsheet. I would like to limit the cell range so that dragging a cell is easier, at the moment it is very common to accidentally drag past the bottom of the sheet. so now I tend to drag upwards as this works because you stop at the top of the sheet.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ooops, don't know what happened there.

    Paste this into the Workbook_Open() sub; it will restrict the scroll area on all sheets in your workbook to the range A1:M100 (change this to suit your purposes). Hope this is OK.

    Dim ws As Worksheet

    For Each ws In Worksheets
    ws.ScrollArea = "A1:M100"
    Next ws

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Southampton UK
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the reply, but sorry to sound thick, can you explain this to me a little bit more, where do I paste the code exactly, into a cell on the worksheet?

  5. #5
    Guest

    Default

    It's visual basic code so to enter it right-click on any sheet tab in your workbook and select view code. This will open the VB editor. In the left hand window will be the 'Project Explorer', containing names of your sheets and an entry of 'ThisWorkbook'. Right-click on 'ThisWorkbook' and select 'View code' again. In the right-hand window is the code window with two drop-downs, change the left drop-down from 'General' to 'Workbook'. This will create the Workbbok_Open() sub for you. Copy the code above into this. Your code should then look like this: -

    Private Sub Workbook_Open()

    Dim ws As Worksheet

    For Each ws In Worksheets
    ws.ScrollArea = "A1:M100"
    Next ws

    End Sub

    Save your workbook and close it. Now, when you re-open it, you will get a message asking if you want to run macros, click Yes, the code will then be activated and your scroll-areas will be set as above.

    As I said above, change the line: -

    ws.ScrollArea = "A1:M100"

    to reflect the areas you want to be able to scroll to, e.g. if you want the area A1:N500 to be scrollable, change it to: -

    ws.ScrollArea = "A1:N500"

    Hope this is OK and good luck .

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dunno what I'm doing tonight, the above was from me.

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    Southampton UK
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, that work great!

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    BTW to add

    try delete the entire rows and then the columns not required. this will grey out the missing r / c if to end of range and colunns IV and 65536

    OR in VB Editor change the scroll area to ABC as req will stop the scrolls

    Also in tools option can deselect scroll bars H + V which also limits movements

    HTH


    _________________
    Good Luck
    HTH

    Rdgs
    ==========
    Jack in the UK

    [ This Message was edited by: Jack in the UK on 2002-03-02 15:18 ]

  9. #9
    Guest

    Default

    On 2002-03-02 08:55, Arun Sharma wrote:
    How can I limit the number of cells in my spreadsheet. I would like to limit the cell range so that dragging a cell is easier, at the moment it is very common to accidentally drag past the bottom of the sheet. so now I tend to drag upwards as this works because you stop at the top of the sheet.

    Another way :-

    Select all rows after the bottom of your data range and Format>Row>Hide


Some videos you may like

User Tag List

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
  •