Excel Unplugged

Scroll trouble in Excel (scrolling beyond your data)

Sometimes in Excel, people insert data in places where they weren’t supposed to. And even if they remove any trace of their wayward journey, a scroll bar remembers it. I call this Scroll Trouble in Excel. This post will show you how to make Excel “reset” the Scroll Bar in three different ways.

If you like learning from Video more that you do from Blog Posts, here is a link to a video on my YouTube channel teaching you basically the same thing: https://bit.ly/3wOno82

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.

Scroll trouble in Excel

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.

Scroll trouble in Excel

But then you delete this entry and Save the file. One might expect for the scroll bar to go back to row 100 as the bottom line, but it does not. Now we have Scroll Trouble in Excel.

Scroll trouble in Excel

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 you can get rid of Scroll Trouble in Excel in three different ways.

Method 1: Hiding Rows or Columns (not the best way)

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.

Scroll trouble in Excel

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.

Scroll trouble in Excel

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.

Method 2: Truly deleting the data (the best way)

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

Scroll trouble in Excel

I hope that you noticed a distinction to the previous method where here you still have rows bellow 100 visible and ready to go!

Method 3: Using the VBA editor or VBA code

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

Scroll trouble in Excel

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)).

Scroll trouble in Excel

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.

Here is a video version of this post:

Happy scrolling.