You can scroll down, scroll to the top and scroll to a cell with the VBA ScrollRow and ScrollColumn properties. You can also scrow in increments using the SmallScroll property.
The key to scrolling your windows with Excel VBA is to understand that each of the scroll properties fall under the ActiveWindow object. For example, to scroll your row with VBA, you would call the ActiveWindow.ScrollRow property.
I’m going to give you a bunch of examples in this tutorial that you can adapt for your own VBA project.
Examples - VBA Scroll
There aren’t many reasons to have to visually navigate around a spreadsheet using VBA, but occassionally you find yourself needing to. For example, if you’re printing output to a certain column, you may want to jump to that column the moment your macro is finished so the user will know. The VBA scroll properties are a great way to do that.
Take a look at these examples.
VBA Scroll to Top of Page
Sub ScrollToTop() 'This macro scrolls to the top of your spreadsheet ActiveWindow.ScrollRow = 1 'the row you want to scroll to End Sub
Make powerful macros with our free VBA Developer Kit It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
VBA Scroll to Top-Left of Page
Sub ScrollToTopLeft() 'This macro scrolls to the top left of your spreadsheet (cell A1) ActiveWindow.ScrollRow = 1 'the row you want to scroll to ActiveWindow.ScrollColumn = 1 'the column you want to scroll to End Sub
VBA Scroll to Cell
Sometimes you want a specific cell to be in the upper left of your window. This macro scrolls untill cell
Sub ScrollToCell() 'This macro scrolls until cell B5 is in the upper left ActiveWindow.ScrollRow = 5 'the row you want to scroll to ActiveWindow.ScrollColumn = 2 'the column you want to scroll to End Sub
You can adapt this macro to scroll to a specific column or specific row by removing the property you don’t care about.
So far we’ve shown you how to use the ScrollRow and ScrollColumn properties to scroll to a specific cell position on your spreadsheet.
In this section, we’ll introduce the VBA SmallScroll property, which lets you scroll up, down, left and right by whatever increment you want. This means you can navigate by a relative number of cells instead of navigating to a fixed position. I’ll show some examples in a minute to better explain what I mean.
But first let’s talk syntax. Like the ScrollRow and ScrollColumn properties, the SmallScroll property is a member of the ActiveWindow property.
The SmallScroll property accepts 4 arguments:
Each argument is optional so you can enter as many or as few arguments as you want. SmallScroll won’t cause an error if you try scrolling too far in one direction. For example, if you’re on the top row and you try scrolling up, your spreadsheet will just stay on the top row and your macro will continue without changing anything.
The SmallScroll property is useful when you know you need to scroll, but you don’t know what cell you need to scroll to. For example, if you know you need to scroll down 1 cell but you don’t know (or don’t care) what cell you’re on at the moment, you can use SmallScroll to scroll one cell from your current position.
VBA Scroll Down
Sub ScrollDown() 'this macro scrolls down 1 cell. ActiveWindow.SmallScroll Down:=1 End Sub
VBA Scroll Right
Sub ScrollRight() 'this macro scrolls right 2 cells. ActiveWindow.SmallScroll ToRight:=-2 End Sub
The SmallScroll property is actually pretty funny. You can enter a negative number to scroll in the opposite direction. For example, the line
actually scrolls left 2 cells even though you used the ToRight argument. That makes perfect sense, but I still find it kind of amusing. :-)
VBA Scroll All Sheets to Same Position
This example will loop through each sheet in your workbook and position the window in the same cell. It won’t select the same cell in each window, but the same cell will be in the top left of the window.
Sub ScrollAllSheets() 'This macro scrolls each sheet in your workbook to cell E1. 'NOTE: It does not select cell E1. It just positions the sheet ' so cell E1 is in the top left. Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Activate ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollRow = 1 Next ws End Sub
That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.