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: 36,873

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 5, 2018.

I should keep these four permanently.
Originally Posted by rekrap View Post
JMO is right
Originally Posted by campbell View Post
I agree with JMO.
Originally Posted by Westley View Post
And def agree w/ JMO.
Originally Posted by MG View Post
This. And everything else JMO wrote.
And this all purpose permanent quote:
Originally Posted by Dr T Non-Fan View Post
Yup, it is always someone else's fault.
Originally Posted by Sredni Vashtar View Post
I feel like ERM is 90% buzzwords, and that the underlying agenda is to make sure at least one of your Corporate Officers is not dumb.
Originally Posted by PeppermintPatty View Post
But sometimes you get someone who has that charming mix of "don't care" and "don't get it", and there's just not much you can do.
Reply With Quote
Old 05-24-2007, 05:46 PM
dumples dumples is offline
Join Date: Sep 2003
Posts: 1,246

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

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 10:25 AM.

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