How do I create an Excel Macro that will auto scroll and loop back to the beginning?

Question

So I'm very much a beginner to VBA/macros in excel, so please bear with me.

Ultimately, we display spreadsheets up on our tvs in the shop and I'm looking to implement an auto scroll function that will scroll to the end of the data slowly and loop back up when it reaches the end, doing this continuously.

I have this so far:

Sub Macro12()
    Range("A1").Select
Do Until IsEmpty(ActiveCell)
    ActiveWindow.SmallScroll Down:=2
Loop
End Sub

It scrolls, but it just keeps going. How can I make it loop back to the beginning and start again on a continuous loop?

Thanks!

Check out this [SO question](https://stackoverflow.com/questions/24377197/iterating-through-populated-rows-in-excel-using-vba), it might help. You can also do a google search for "scroll through rows". — GMalc, Jun 08 '18 at 15:26

score 3 · Answer 1 · answered Jun 08 '18 at 15:44

3

This works for me:

Sub Macro12()
Dim lastRow As Long, i As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lastRow Step 2
    Cells(i, 1).Select
    ActiveWindow.SmallScroll down:=2
    Application.Wait (Now + TimeValue("0:00:05")) 'Wait five seconds before continuing.
    If i = lastRow - 2 Or i = lastRow - 1 Then
        i = 0
        Cells(1, 1).Select
    End If
Next i
Debug.Print (i)

End Sub

But note, it's an infinite loop. This could (probably) eventually cause Excel to crash.

answered Jun 08 '18 at 15:44

BruceWayne

22,923
15
65
110

1

you could wrap the loop in a timer to stop its infinacy (i made that word up!) – Scott Holtzman Jun 08 '18 at 15:48
This is fantastic! One small question: let's say the last row with information in it is row 35, but I don't want it to scroll all the way down to 35, I want it to stop at 23 and loop back (because scrolling to 35 would just display the info in 35 and a bunch of blank rows), would I just declare that here: For i = 1 To lastRow Step 2 – rjwctm21 Jun 08 '18 at 15:49
1

@user1036131 - Yes, if you know the specific row, just do `For i = 1 to 23 Step 2`. – BruceWayne Jun 08 '18 at 15:51
2

@user1036131 - you would wrap a `Do ... Loop Until` statement around the `For Each ... Next` loop. Something like `Do ... Loop Until Now > TimeValue("15:00:00")` to stop it after 3pm. Or you could stop it after a specified period of time if you define that before hand into a variable. There's lots of options, but I would write a separate question if you get stuck on that. – Scott Holtzman Jun 08 '18 at 16:00

Marcucciboy2 · Answer 2 · 2018-06-27T18:41:18.060

1

Here's one that scrolls about a fifth of the page at a time until it hits the bottom of the page. You can adjust the timer if you feel it moves too quickly/slowly

Sub ScrollToBottom()

    Dim lastrow As Integer
    lastrow = Range("A" & Rows.count).End(xlUp).row

    While Intersect(Rows(lastrow), ActiveWindow.VisibleRange) Is Nothing
        Application.Wait (Now + TimeValue("0:00:01"))
        ActiveWindow.SmallScroll down:=Round(ActiveWindow.VisibleRange.Rows.count / 5, 0)
    Wend

    Application.Wait (Now + TimeValue("0:00:03"))
    ActiveWindow.ScrollRow = 1

End Sub

edited Jun 27 '18 at 18:41

answered Jun 08 '18 at 15:51

Marcucciboy2

3,156
3
20
38

same adjustment with mine if you wanted to scroll only to row 23 - just set `lastrow = 23` – Marcucciboy2 Jun 08 '18 at 16:01
to make it scroll slower you can add to the timer, to make it scroll *less* you can increase the number 3 in `ActiveWindow.VisibleRange.Rows.count / 3` – Marcucciboy2 Jun 08 '18 at 16:05

score 0 · Answer 3 · answered Jun 08 '18 at 16:08

Here's my take on this:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub LoopBackAndForth()
    Dim i As Long, j As Long
    Dim rng As Range

    i = 1
    With ActiveSheet ' change sheet if required
        Set rng = .Range("A" & i)
        rng.Select
        Do Until rng.Value = vbNullString
            i = i + 1
            Set rng = .Range("A" & i)
            Sleep 75 ' the optimal value may vary from pc to pc
            ActiveWindow.SmallScroll down:=1 ' changed from 2 in the original post
        Loop

        For i = i To 1 Step -1
            Set rng = .Range("A" & i)
            Sleep 75 ' the optimal value may vary from pc to pc
            ActiveWindow.SmallScroll up:=1 ' changed from 2 in the original post
        Next i

    End With
End Sub

How do I create an Excel Macro that will auto scroll and loop back to the beginning?

3 Answers3