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
Actuarial Jobs

Visit our site for the most up to date jobs for actuaries.

Actuarial Salary Surveys
Property & Casualty, Health, Life, Pension and Non-Tradtional Jobs.

Actuarial Meeting Schedule
Browse this year's meetings and which recruiters will attend.

Contact DW Simpson
Have a question?
Let's talk.
You'll be glad you did.

Thread Tools Display Modes
Old 05-24-2007, 03:18 PM
soliloquist soliloquist is offline
Join Date: Apr 2007
Posts: 8
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: 32,669

I have found that just saving it resets the {end} location. It may depend on which version of Excel you are using.
Carol Marler, Ex-Actuary, Dedicated to Being Retired
Just My Opinion: It's not an actuarial opinion; I'm no actuary, although I used to be one.

My latest favorite quote, updated Oct 7, 2015
Originally Posted by Dr T Non-Fan View Post
So, yeah, probably wasn't a good idea to start an insurance company without any money.
Reply With Quote
Old 05-24-2007, 05:46 PM
dumples dumples is offline
Join Date: Sep 2003
Posts: 1,199

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,199

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 04:04 PM.

Powered by vBulletin®
Copyright ©2000 - 2015, 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.14202 seconds with 7 queries