how to reset the vertical scroll bar via macro?

[brianh111]

I saw this posted on the forum about a year ago, but am unable to locate the response. I'd appreciate any help.

My problem is that I have a sheet with about 500 rows of data, but the vertical scroll bar is so "minimized" that if I drag the scroll bar to the bottom, I end up on row 10000. How can I reset the scroll bar so that if I drag it to the bottom, it will end on row 500? (Rows 500 thru 10000 have no data). Thanks again for any help.

Brian

 

[Richie(UK)]

Hi Brian,

Welcome to the board.

In my experience the vertical scroll bar takes you down to the last used row. That is, Excel's idea of the last used cell!

If you press Ctrl + End where do you end up, Row 500 or Row 10,000? I suspect that a cell in Row 10,000 may previously have had data in it and is retaining some formatting or has its protection status set to unlocked. If this is the case then Excel will see that as the Last Cell and adjust the scroll bar accordingly.

Are you familiar with VBA / Macros? If so, give the following routine a try (From the excellent [no pun intended] book "Excel 2002 VBA Programmer's Reference"):

<pre>Sub DeleteUnusedFormats()
Dim lLastRow As Long, lLastColumn As Long
Dim lRealLastRow As Long, lRealLastColumn As Long

With Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = .Row
lLastColumn = .Column
End With
lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , _
xlByRows, xlPrevious).Row
lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , _
xlByColumns, xlPrevious).Column

If lRealLastRow < lLastRow Then
Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete
End If
If lRealLastColumn < lLastColumn Then
Range(Cells(1, lRealLastColumn + 1), Cells(1, lLastColumn)) _
.EntireColumn.Delete
End If

ActiveSheet.UsedRange 'resets LastCell

End Sub</pre>
HTH

PS. As always with routines that delete sections of a workbook, make sure that you try it on a back-up copy first in case you get unexpected results!