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


Upload your resume securely at https://www.dwsimpson.com
to be contacted when our jobs meet your skills and objectives.


Reply
 
Thread Tools Display Modes
  #1  
Old 05-24-2007, 04:18 PM
soliloquist soliloquist is offline
SOA
 
Join Date: Apr 2007
Posts: 8
Default excel: Scroll Bar Size

Hello,

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?

Thx.
Reply With Quote
  #2  
Old 05-24-2007, 04:22 PM
CyberGuy2004 CyberGuy2004 is offline
Member
SOA
 
Join Date: Feb 2007
Location: HotLanta
Favorite beer: water
Posts: 174
Default

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
  #3  
Old 05-24-2007, 04: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: 30,953
Default

I have found that just saving it resets the {end} location. It may depend on which version of Excel you are using.
__________________
Carol Marler, FSA, MAAA, A Former Actuary
Dedicated to Retirement
Just My Opinion (Although this statement is my opinion, and I am still listed as an actuary, it's still not a statement of actuarial opinion, and you really shouldn't rely on it.)
Reply With Quote
  #4  
Old 05-24-2007, 06:46 PM
dumples dumples is offline
Member
 
Join Date: Sep 2003
Posts: 1,171
Default

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)

Code:
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, _
        searchorder:=xlByRows).Row
    myLastCol = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, lookat:=xlWhole, _
        searchdirection:=xlPrevious, _
        searchorder:=xlByColumns).Column
    On Error GoTo 0
    If myLastRow * myLastCol = 0 Then
        .Columns.Delete
    Else
        .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
  #5  
Old 05-24-2007, 06:48 PM
dumples dumples is offline
Member
 
Join Date: Sep 2003
Posts: 1,171
Default

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
  #6  
Old 05-24-2007, 07:11 PM
notreallyme's Avatar
notreallyme notreallyme is online now
Member
CAS
 
Join Date: Dec 2002
Posts: 19,332
Default

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
Reply

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 03:55 PM.


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