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.


VBA Scroll to Top of Page
VBA Scroll to Top-Left of Page
VBA Scroll to Cell
VBA SmallScroll
VBA Scroll Down
VBA Scroll Right
VBA Scroll All Sheets to Same Position
Closing Thoughts


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

Becoming a VBA expert isn't hard
Over 5000 members are improving their VBA skills for free with our email tutorials. Why don't you join them? Our experts share time-saving VBA tips and we'll give you access to our huge macro library - it's sure to speed up your macro development.

I want to get your free VBA material


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 B5 is in the upper left. Cell B5 has a row number of 5 and a column number of 2.

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.


VBA SmallScroll

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:

  1. Down
  2. Up
  3. ToRight
  4. ToLeft

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.

Here’s an example:

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

ActiveWindow.SmallScroll ToRight:=-2

actually scrolls left 2 cells even though you used the ToRight argument. That makes perfect sense, but I still find it kind of amusing. :-)


Becoming a VBA expert isn't hard
Over 5000 members are improving their VBA skills for free with our email tutorials. Why don't you join them? Our experts share time-saving VBA tips and we'll give you access to our huge macro library - it's sure to speed up your macro development.

I want to get your free VBA material

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

Comments

Please subscribe to my free wellsrPRO VBA Training Program for more VBA tips and share this article with your friends on Facebook,and Twitter.