So this piece of code will do the same as the first sample in Method 3. Now the second way of doing this within Method 3 is actually doing the same thing but with VBA code. This can be quite useful in some situations but it is not as elegant and not as useful as Method 2 in this case. The Scroll Bar will not adjust as one might expect but if you try to scroll below row 100, you just can’t :). Something very interesting will happen then. 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)). To get there either press Alt + F11 or go to DEVELOPER/Visual Basic The first one only requires you to use the VBA editor. 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 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 This will delete those rows and any formatted cells that lurked in the depth bellow the data :). After selecting all the rows that don’t contain data press Ctrl + – (so Control and minus). 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. It’s very important that you understand that Delete key only deletes the cell content, it does not remove the cell format. Removing the invisible data that still resides in cell A200. Now in this case, we will truly deal with the root of the problem. Method 2: Truly deleting the data (the best way) 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.īut 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. 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. Whereas this is not an ideal solution, it does provide you with the desired result. Method 1: Hiding Rows or Columns (not the best way) Here is how you can get rid of Scroll Trouble in Excel in three different ways. 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). 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. One might expect for the scroll bar to go back to row 100 as the bottom line, but it does not.
All as it should be so far.īut then you delete this entry and Save the file.
And it’s not a shock that at this point, the vertical Scroll Bar goes all the way to row 200. Now someone decides to put something non trivial (something that includes some formatting like a date or currency) into cell A200. At this point the vertical Scroll Bar goes from row 1 to row 100. Just for reference, this is what we are talking about. 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:
#HOW TO HIDE HORIZONTAL SCROLLBAR IN EXCEL HOW TO#
This post will show you how to make Excel “reset” the Scroll Bar in three different ways. And even if they remove any trace of their wayward journey, a scroll bar remembers it. Sometimes in Excel, people insert data in places where they weren’t supposed to.
0 kommentar(er)
