how to reset the vertical scroll bar via macro?

brianh111

New Member
Joined
Nov 7, 2002
Messages
10
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
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!
 

Forum statistics

Threads
1,144,765
Messages
5,726,173
Members
422,659
Latest member
RGP268

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top