Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Slow modification (2003)

    Longers, I have a spread sheet that is not very big (in terms of data), but it is very slow to insert/delete rows or columns or add copied tabs - like 5 - 10 minutes each

    It uses mainly lookup type formula ike
    =IF(D9="","",IF(I9="","",IF(I9>0,(VLOOKUP(CONCATEN ATE(H9&I9),(Lookup!$C$10:$G$150),4,FALSE)),))), in 6 colums, with some conditional formatting and =OFFSET(Lookup!$J$17:$J$21,0,MATCH(template!$D9,Ri sk_type,0)) - in data validation also
    ="Risk Assessment For"& " - " & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A 1))-FIND("]",CELL("filename",A1))) to put tab name in cell.

    When I add a new tab it adds about 2mgs to the size of the spreadsheet (i have some with 10 tabs)

    Any thoughts on why it is so slow or what I could do to speed things up,etc

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Slow modification (2003)

    Lots of lookup-type formulas tend to slow down recalculation, but 5 to 10 minutes seems excessive. Without seeing the actual workbook it is hard to see what causes this. You could set calculation to manual, and only turn it on when you need to have updated results.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow modification (2003)

    Thanks for the quick reply Hans

    I'll give it a try - I'll also try to strip down a workbook and post it if you can have a look

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow modification (2003)

    Does the workbook contain any macro code, and particularly any code in event routines?
    Legare Coleman

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Slow modification (2003)

    Dean,
    You might want to look at replacing your VLOOKUPs with INDEX/MATCH combinations, as VLOOKUPs recalculate if any change is made anywhere in the reference table, whereas the INDEX/Match formula references specific columns. OFFSET formulas can also cause a bit of slowdown in recalculation, so if you can find a way to replace them or reduce the number of them, that might help.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow modification (2003)

    Legare - Thanks for the reply

    The only code it contains is below used to widen colums

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static lngCol As Long
    If Target.Count > 1 Then Exit Sub
    Select Case Target.Column
    Case 8, 9, 13, 14, 20, 21
    Target.Columns.ColumnWidth = 30
    End Select
    Select Case lngCol
    Case 8, 9, 13, 14, 20, 21
    Columns(lngCol).ColumnWidth = 5
    End Select
    lngCol = Target.Column
    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow modification (2003)

    Rory,

    Thanks - I'll try to replace the vlookup's and see how it goes

    Regards

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Slow modification (2003)

    Dean,
    Re your reply to Legare, do you really want the column sizes changed everytime you select a different cell in the worksheet??
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow modification (2003)

    Rory

    Thanks for the reply - I use this as the cells in the selected columns contain a list that is cut off if the column width remains the same - any other thoughts?

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Slow modification (2003)

    You could try using the Worksheet_Change event and add a check to see if the change occurs in a column you are interested in? Your current code will run every time you select a cell whether or not any data has changed.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •