Actuarial Outpost
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

DW Simpson International Actuarial Jobs
Canada  Asia  Australia  Bermuda  Latin America  Europe

Thread Tools Display Modes
Old 05-24-2007, 03:18 PM
soliloquist's Avatar
soliloquist soliloquist is offline
Join Date: Apr 2007
Posts: 13
Default excel: Scroll Bar Size


I have a question regarding adjusting the size of the scrollbars in excel. As you fill in more and more of a worksheet in excel it automatically adjusts the size of the scrollbar (smaller and smaller) so that you can tell approximately where you are in the total used space.

The problem is when you clear part of the worksheet it does not adjust the size back to accurately represent the total used space.

For example: If you enter a number in the last cell on a sheet (IV,65536) the scrollbar will get extremely small. If you then delete the contents of this cell the scrollbar remains extremely small.

So is there a way to "reset" the size of the scrollbars?

Reply With Quote
Old 05-24-2007, 03:22 PM
CyberGuy2004 CyberGuy2004 is offline
Join Date: Feb 2007
Location: HotLanta
Favorite beer: water
Posts: 174

Save and close the workbook, then re-open it. Usually works for me. If it doesn't work, I probably have not cleared a "far-out" cell of format or blanks.
93.2% of statistics are made up on the spot.
Reply With Quote
Old 05-24-2007, 03:31 PM
JMO's Avatar
JMO JMO is offline
Carol Marler
Join Date: Sep 2001
Location: Back home again in Indiana
Studying for Nothing actuarial.
Posts: 35,537

I have found that just saving it resets the {end} location. It may depend on which version of Excel you are using.
Carol Marler, "Just My Opinion"

Pluto is no longer a planet and I am no longer an actuary. Please take my opinions as non-actuarial.

My latest favorite quotes, updated Mar 15, 2017.

I should keep these two permanently.
Originally Posted by rekrap View Post
JMO is right
Originally Posted by campbell View Post
I agree with JMO.
Originally Posted by Colonel Smoothie View Post
The ones who complain that they're too good for that kind of work really aren't that good.
Two really useful all-purpose responses. Use one or both:
Originally Posted by JasonScandopolous View Post
To which I say: duh and lol.
Originally Posted by Colonel Smoothie View Post
actuarial isn't the only good career out there.
All round good advice:
Originally Posted by snikelfritz View Post
Don't live your life by other people's expectations.
Originally Posted by Dr T Non-Fan View Post
If you feel you have to lie, then you're don't have enough integrity to be an actuary.
Reply With Quote
Old 05-24-2007, 05:46 PM
dumples dumples is offline
Join Date: Sep 2003
Posts: 1,229

Like they said, saving it does the trick, but it takes more than "clearing" the area (using the delete key), you need to delete (ctrl -) the area to be sure to remove any formatting.

if you're interested in a visual basic solution that resets the used range in all sheets in the active workbook, this is what I use. (this really cuts down the file size of some files)

Sub TrimSheets()
Dim myLastRow As Long
Dim myLastCol As Long
Dim wksht As Worksheet
Dim dummyRng As Range
For Each wksht In ActiveWorkbook.Worksheets
  With wksht
    myLastRow = 0
    myLastCol = 0
    Set dummyRng = .UsedRange
    On Error Resume Next
    myLastRow = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, lookat:=xlWhole, _
        searchdirection:=xlPrevious, _
    myLastCol = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, lookat:=xlWhole, _
        searchdirection:=xlPrevious, _
    On Error GoTo 0
    If myLastRow * myLastCol = 0 Then
        .Range(.Cells(myLastRow + 1, 1), _
          .Cells(.Rows.Count, 1)).EntireRow.Delete
        .Range(.Cells(1, myLastCol + 1), _
          .Cells(1, .Columns.Count)).EntireColumn.Delete
    End If
    Set dummyRng = .UsedRange
  End With
Next wksht
End Sub
Reply With Quote
Old 05-24-2007, 05:48 PM
dumples dumples is offline
Join Date: Sep 2003
Posts: 1,229

That macro causes (#Ref) problems in formulas that reference blank cells on the side or bottom of your spreadsheet.

Why are you pointing formulas at blank cells anyway?
Reply With Quote
Old 05-24-2007, 06:11 PM
Posts: n/a

I typically just select the bottom row to keep and hit Shift-End-Down then Delete Rows.

This should do the trick.
Reply With Quote

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

All times are GMT -4. The time now is 12:18 AM.

Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.14382 seconds with 7 queries