Limiting the range of cells in a worksheet

Arun Sharma

New Member
Joined
Mar 1, 2002
Messages
4
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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?
 
Upvote 0
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 :).
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top