Sometimes in Excel, we are just an innocent bystander when enthusiasm gets the better of people and they start drifting around Excel inserting data in places where they weren’t supposed to. And even if they are so kind to remove any trace of their wayward journey, a scroll bar remembers it all. This post will show you how to make her forget in three different ways.
Just for reference, this is what we are talking about.
We have Data in Cells A1:A100. At this point the vertical Scroll Bar goes from row 1 to row 100.
Now someone decides to put something non trivial (something that includes some formatting like a date or currency) into cell A200. And it’s not a shock that at this point, the vertical Scroll Bar goes all the way to row 200. All as it should be so far.
But then you delete this entry and Save the file. Now one might expect for the scroll bar to go back to row 100 as the bottom line, but it does not.
Now here’s a famous saying. This is a feature! Excel thinks that since you have taken the time to format cell A200 that cell must be important to you and you are just waiting to input some data into it and therefore it still gives you the ability to move swiftly to it by using the scroll bar. But our problem is that cell A200 has no value for us and we would like for the scroll bar to only go from the first row to the last row with data (in this case row 100).
Here is how that can be achieved in three different ways.
Whereas this is not an ideal solution, it does provide you with the desired result. What you do is you select all the Rows bellow your data (or Columns to the right of your data, depending on which Scroll Bar you wish to adjust), Right Click and select Hide.
Since this will hide all the rows from the hundredth down, it will therefore adjust the Scroll bar which now does stop at row 100.
But just to be clear, this means that every time you wish to add data, you would have to unhide the rows you need and then add data which can be quite time consuming so in those cases, choose one of the following ways to get a desired result.
Now in this case, we will truly deal with the root of the problem. Removing the invisible data that still resides in cell A200. It’s very important that you understand that Delete key only deletes the cell content, it does not remove the cell format. To do that you must delete that cell! The best way to do that is to select the first row bellow the data and down to the last row. After selecting all the rows that don’t contain data press Ctrl + – (so Control and minus). This will delete those rows and any formatted cells that lurked in the depth bellow the data :). But still the Scroll Bar will remain as it was until you save that file! And right after you do, you get what you want
I hope that you noticed a distinction to the previous method where here you still have rows bellow 100 visible and ready to go!
It’s actually two separate methods. The first one only requires you to use the VBA editor. To get there either press Alt + F11 or go to DEVELOPER/Visual Basic
In the VBA Editor window choose the Excel Sheet where the Scroll Bar has stopped cooperating with you and in the Properties Window (if you can’t see it, choose View/Properties Window or press F4) adjust the ScrollArea Property to the desired value (in this sample that is A1:A100 (the dollar signs will be added automatically)).
Something very interesting will happen then. The Scroll Bar will not adjust as one might expect but if you try to scroll below row 100, you just can’t :). This can be quite useful in some situations but it is not as elegant and not as useful as Method 2 in this case.
Now the second way of doing this within Method 3 is actually doing the same thing but with VBA code.
Sub SetScrollArea() ActiveSheet.ScrollArea = "a1:a100" End Sub
So this piece of code will do the same as the first sample in Method 3.
Keep in mind, this is also a great way to protect your sheet or data beyond a certain point without actually using Sheet protection.