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

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: 34,607

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 Oct 5, 2016.

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 ActSciMan View Post
Probability 0 and impossible are not the same thing.
Originally Posted by whoanonstop View Post
Some people want the worst of both worlds.
Originally Posted by aNYthing View Post
Oops, my phone auto corrected Deloitte to Despite.

Originally Posted by nonlnear View Post
Just so that nobody reading the thread gets the impression that this is not an objectively terrible idea.
DTNF's excellent advice:
Originally Posted by Dr T Non-Fan View Post
Be great.
Originally Posted by JohnLocke View Post
This is obviously true because I said so.
Originally Posted by Hydraskull View Post
Duration is math, but ALM isn't.
Originally Posted by BG5150 View Post
Does no one learn FORTRAN any more?
Reply With Quote
Old 05-24-2007, 05:46 PM
dumples dumples is offline
Join Date: Sep 2003
Posts: 1,223

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

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

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