Excel Unplugged

Scroll trouble in Excel (scrolling beyond your data)


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.

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. Now one might expect for the scroll bar to go back to row 100 as the bottom line, but it does not.

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 that can be achieved 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.

Happy scrolling.

Comments 7

  1. Larry S says:

    Instead of deleting the row to remove the formatting, you can simply use the Clear All feature on the ribbon. Select the cell(s) that has/had the data, select the Home tab, in the Editing group, select the drop arrow beside Clear and select Clear All. You will still need to save the file to get the scroll bar it reset.

    1. Hi Larry!
      You are right and I also pointed out in the article that deleting the rows is not the best idea. However, Method 2 from the post does exactly what you suggest but far quicker. In effect, clearing those cells will do just the same as Ctrl + – but Ctrl + – is much quicker that going Home/Clear/Clear All. But again, although it’s not the same thing, it has the same effect as Method 2.

  2. D says:

    I have been searching for a solution to this problem all night. Method 1 is not practical for me, and method 2 does not work (though I’ve tried about 100 variations of it at this point). I have a spreadsheet with almost 50,000 active rows, but the scroll bar is taking me down to over a million, so method three doesn’t help the situation much as the shrunken scroll bar still cannot move with any trace of accuracy. Any other ideas?

  3. john says:

    Thanks for this tip. Worked as described!

    1. john says:

      forgotten to add: Method 2

    2. @John

      Nice to hear that John… So now you’re one step closer to eternal happiness 🙂

  4. Eric says:

    Nice one Gasper – Method 2 worked a treat, thanks (I never tried the others)….

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: