You are here:

Excel/Auto-Scrolling help

Advertisement


Question
QUESTION: Hi Tom hope all is well with you today. I am using Excel 2007.

I am looking to auto scroll down from row 2 to the last row with text and then go back to the top in this case D2 and to repeat the scrolling function.  I would like it to continually scroll also.  Is there also a way to have a delay on scroll?  Lets say 15 seconds and to scroll 10 rows at a time.

Any help would be greatly appreciated.

ANSWER: Anand Shah

I offer no guarantees because I have no idea what you are doing or need to do.  This is an adaptation of a routine I wrote a long time ago and it worked for that person.  It is not tested with the adaptations.

You can try code like this.

It you type anything in J1 (so J1 is not empty), the scrolling will stop.


Make Limit larger to make scrolling slower.  Make it smaller to make scrolling faster


Sub ScrollRows()
Dim ii As Long, i As Long
Dim lastrow As Long, iii As Long
Dim limit As Long, bStop As Boolean
Range("J1").ClearContents
Range("J1").Activate

limit = 10000

bStop = False
Do
DoEvents
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For iii = 2 To lastrow Step 10  '<== number of rows to scroll
DoEvents
ActiveWindow.ScrollRow = ii
DoEvents
Application.ScreenUpdating = True
DoEvents
For i = 1 To limit  '< make this number bigger if you need to slow the scroll down
  DoEvents
  If ii > limit Then
   ii = 0
  Else
   ii = ii + 1
  End If
  If Not IsEmpty(Range("J1")) Then Exit Sub
  DoEvents
Next
DoEvents
Next iii
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
DoEvents
Loop While IsEmpty(Range("J1")) And bStop = False

End Sub

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hi Tom.  I tried the code and a run time error came up stating that it was unable to set the scrollrow property of the Window class.  Here was the debug:

ActiveWindow.ScrollRow = ii

any ideas?

Thanks

Answer
the ii should be iii

I also changed the cell to tell it to stop to J2.  Even though J2 will not be visible some of the time, since it is selected, you can just type a letter and hit enter.  (Otherwise use Ctrl+Break to halt the macro)

I tested this and it worked for me.  Just changing 10000 to 100000 made a big difference in the scroll rate.

Sub ScrollRows()
Dim ii As Long, i As Long
Dim lastrow As Long, iii As Long
Dim limit As Long, bStop As Boolean
Range("J2").ClearContents
Range("J2").Activate

limit = 10000

bStop = False
Do
DoEvents
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For iii = 2 To lastrow Step 10  '<== number of rows to scroll
DoEvents
ActiveWindow.ScrollRow = iii
DoEvents
Application.ScreenUpdating = True
DoEvents
For i = 1 To limit  '< make this number bigger if you need to slow the scroll down
  DoEvents
  If ii > limit Then
   ii = 0
  Else
   ii = ii + 1
  End If
  If Not IsEmpty(Range("J2")) Then Exit Sub
  DoEvents
Next
DoEvents
Next iii
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
DoEvents
Loop While IsEmpty(Range("J2")) And bStop = False

End Sub

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.