Limiting the range of cells in a worksheet

[Arun Sharma]

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.

 

[Mudface]

[No message]

 

[Mudface]

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

 

[Arun Sharma]

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?

 

[Guest]

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 .

 

[Mudface]

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

 

[Arun Sharma]

Thanks, that work great!

 

[Jack in the UK]

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

 

[Guest]

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