Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Perth, Western Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sheet becomes big and slow (Excel 2000 SR 1a)

    Dear all,
    I have written a macro that performs a simple function, cutting a slab of data from one workbook and pasting it into another workbook in a location such that calculations are carried out on the data, the results are then copied and pasted on another sheet in the second workbook. All seemed to be fine, except that the more times I did this the larger the workbook got and the slower it became. The size increase was much more than would be expected due to the amount of extra data and the slowing down was ridiculous considering the size of the work book (<20Mb). The slowing down was unusual in that it depended upon which sheet was open and which cells on the sheet were in view, but not what was in them because when I deleted columns the slowing down did not change. I copied the offending sheet into a new workbook and deleted all columns and rows from the sheet by right clicking in the top left corner and selecting delete. The workbook with nothing in it, i.e. one empty sheet (verified by pressing control end and ending ip in cell A1) was 2.6Mb. I tried the trick of saving it in Excel 2000/97/95 and the empty sheet blew out to 11Mb. Any ideas what is causing this problem will be appreciated.
    Thanks
    Don
    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sheet becomes big and slow (Excel 2000 SR 1a)

    Don't know about the size problem, but for the speed problem, turning off screen updates while the macro is running and condensing code which frequently uses [object].Select can help, see Andrew's, Legare's & Sammy's responses to <!post=Excel Post "freezing ...",121726>Excel Post "freezing ..."<!/post>. To which I can only add, if the WBs contain a lot of formulas, turn off automatic recalculation at the beginning of the macro code:

    Include at the beginning of the code:
    ' Create variable to store Excel Recalc Option Setting xlCalculation:
    Dim intCalcSet As Integer
    ' Save present setting for xlCalculation
    intCalcSet = Application.Calculation
    ' Turn off recalcs... speeds up running of code in WB's with lots of formulas
    Application.Calculation = xlCalculationManual

    At appropriate point in the code when you need recalc
    Application.Calculate

    Insert at the end of the code:
    ' When done... recalc and restore xlCalculation setting back to user preference
    Application.Calculate
    Application.Calculation = intCalcSet

    If these don't help, might be worthwhile for you to post the macro.
    -John ... I float in liquid gardens
    UTC -7DS

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Location
    Perth, Western Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet becomes big and slow (Excel 2000 SR 1a)

    Thanks for your reply John.

    I haven't used the procedure for turning of the calcs during a macro before, but it is something that I will use in the future. Unfortunately, I do not think it is the solution to this problem. When I say the spreadsheet has slowed down it is also when just looking at the spreadsheet, not just when the macro is running. After the macro has run its course and I'm trying to scroll around the screen it can take up to 1 minute to scroll one page on some sheets of the workbook, but behave normally on others. I have turned the calculations to manual and this makes no difference. I suspect it has something to do with the huge amount of wasted space. The sheet that I've copied from the workbook and pasted in a new workbook before deleting everything from it is 2.6 Mb and to my vision contains nothing, no data, formulae, objects, names, macros. I'd send it as an example, but even zipped up it is over the 100k limit.
    I've pasted the code extracts that are run during the importing of data, though it may be difficult to follow as only part of the complete project, but the complete project is to big to send.

    Thanks again

    Don

    Sub ImportDataClick()
    Dim ShortName As String, ShortNameCol As Integer
    Dim EnterpriseType As String, EnterpriseTypeCol As Integer
    Dim RowNum As Integer
    Dim ColSel As Integer
    Dim Found As Boolean
    Dim Dataset As String, Template As String
    Dim Appearance As String, ImagineWorkbook As String, DatabaseWorkBook As String
    Dim MacroName As String, Test As Boolean

    'Turn off screen updating
    Application.ScreenUpdating = False

    ImagineWorkbook = Range("Imagine")
    DatabaseWorkBook = ThisWorkbook.Name

    'Find the dataset

    ShortNameCol = 1
    EnterpriseTypeCol = 3

    RowNum = Selection.Row
    ShortName = Cells(RowNum, ShortNameCol)
    EnterpriseType = Cells(RowNum, EnterpriseTypeCol)
    If ShortName <> "" Then
    ColSel = 2
    Found = False

    If EnterpriseType = "Woody" Then
    Dataset = "Woody Dataset"
    Template = "Woody Template"
    Else
    Dataset = "Herby Dataset"
    Template = "Herby Template"
    End If

    Sheets(Dataset).Select
    Do While Cells(2, ColSel) <> ""
    If Cells(2, ColSel) = ShortName Then
    Found = True
    Exit Do
    End If
    ColSel = ColSel + 11
    Loop
    If Found = True Then
    Appearance = Cells(1, ColSel + 1)

    If EnterpriseType = "Woody" Then
    Range(Cells(1, ColSel - 1), Cells(194, ColSel + 9)).Copy
    Windows(ImagineWorkbook).Activate
    Sheets("Woody Template").Select
    Else
    Range(Cells(1, ColSel - 1), Cells(152, ColSel + 9)).Copy
    Windows(ImagineWorkbook).Activate
    Sheets("Herby Template").Select
    End If
    Cells(1, 1).Select
    ActiveSheet.Paste
    Windows(DatabaseWorkBook).Activate
    ActiveSheet.Shapes(Appearance).Copy
    If EnterpriseType = "Woody" Then
    Windows(ImagineWorkbook).Activate
    Sheets("Woody Template").Select
    ActiveSheet.Shapes("WoodyAppearance").Delete
    Cells(196, 2).Select
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft -3
    Selection.ShapeRange.IncrementTop -2.5
    Selection.Name = "WoodyAppearance"
    MacroName = "'" & ImagineWorkbook & "'!NewWoody"
    Test = Application.Run(MacroName)
    Else
    Windows(ImagineWorkbook).Activate
    Sheets("Herby Template").Select
    ActiveSheet.Shapes("HerbyAppearance").Delete
    Cells(154, 2).Select
    ActiveSheet.Paste
    Selection.Name = "HerbyAppearance"
    MacroName = "'" & ImagineWorkbook & "'!NewHerby"
    Test = Application.Run(MacroName)
    End If

    Cells(1, 1).Select
    Sheets("Import Export").Select
    Workbooks(DatabaseWorkBook).Activate
    If EnterpriseType = "Woody" Then
    WoodyCancel
    Else
    HerbyCancel
    End If
    Else
    MsgBox "Error Importing, could not find the dataset"
    End If
    Else
    MsgBox "No dataset selected, click on the row of the data you wish to import"
    End If

    End Sub

    Sub NewWoody()

    Application.ScreenUpdating = False

    Dim EnterpriseName As String 'The name of the new enterprise
    Dim RecordNo As Integer 'How many records are on the Woody Record sheet
    Dim NewName As String 'The new name for the new enterprise worksheet
    Dim NewName2 As String 'NewName with the spaces removed to allow it to be used in range names
    Dim CurrentRow As Integer, CurrentColumn As Integer 'Row and column counters for locating enterprise names on the Layout sheet
    Dim NextName As String, NextNumber As Integer 'Store the details of a fudge rectangle drawn to work out the name of the appearance rectangle

    SheetInfoInit
    NewName = Range("WoodyShortName")
    NewName2 = Replace(NewName, " ", "")

    Sheets(WoodyTemplate).Copy Before:=Sheets(WoodyTemplate)

    'Move all of the bits to keep to the top and rename them with this sheet as precursor
    Application.Goto Reference:="WoodyYearsandQuarters"
    Selection.Cut
    Cells(1, 13).Select
    ActiveSheet.Paste
    Range("WoodyYearsAndQuarters").Name = NewName2 & "YearsAndQuarters"
    ActiveWorkbook.Names("WoodyYearsAndQuarters").Dele te

    Application.Goto Reference:="WoodyReturns"
    Selection.Cut
    Cells(3, 17).Select
    ActiveSheet.Paste
    Range("WoodyReturns").Name = NewName2 & "Returns"
    ActiveWorkbook.Names("WoodyReturns").Delete

    Application.Goto Reference:="WoodyFixedCosts"
    Selection.Cut
    Cells(4, 13).Select
    ActiveSheet.Paste
    Range("WoodyFixedCosts").Name = NewName2 & "FixedCosts"
    ActiveWorkbook.Names("WoodyFixedCosts").Delete

    Application.Goto Reference:="WoodyVariableCosts"
    Selection.Cut
    Cells(5, 17).Select
    ActiveSheet.Paste
    Range("WoodyVariableCosts").Name = NewName2 & "VariableCosts"
    ActiveWorkbook.Names("WoodyVariableCosts").Delete

    Application.Goto Reference:="WoodyWoodySpatialICosts"
    Selection.Cut
    Cells(6, 13).Select
    ActiveSheet.Paste
    Range("WoodyWoodySpatialICosts").Name = NewName2 & "WoodySpatialICosts"
    ActiveWorkbook.Names("WoodyWoodySpatialICosts").De lete

    Application.Goto Reference:="WoodyHerbySpatialICosts"
    Selection.Cut
    Cells(7, 13).Select
    ActiveSheet.Paste
    Range("WoodyHerbySpatialICosts").Name = NewName2 & "HerbySpatialICosts"
    ActiveWorkbook.Names("WoodyHerbySpatialICosts").De lete

    Application.Goto Reference:="WoodyHeight"
    Selection.Cut
    Cells(8, 17).Select
    ActiveSheet.Paste
    Range("WoodyHeight").Name = NewName2 & "Height"
    ActiveWorkbook.Names("WoodyHeight").Delete

    'Fix up the rest of the names
    Range("WoodyCleanup").Name = NewName2 & "Cleanup"
    ActiveWorkbook.Names("WoodyCleanup").Delete
    Range("WoodyCostChange").Name = NewName2 & "CostChange"
    ActiveWorkbook.Names("WoodyCostChange").Delete
    Range("WoodyFirstQ").Name = NewName2 & "FirstQ"
    ActiveWorkbook.Names("WoodyFirstQ").Delete
    Range("WoodyLifespan").Name = NewName2 & "Lifespan"
    ActiveWorkbook.Names("WoodyLifespan").Delete
    Range("WoodyMaxWidth").Name = NewName2 & "MaxWidth"
    ActiveWorkbook.Names("WoodyMaxWidth").Delete
    Range("WoodyMinWidth").Name = NewName2 & "MinWidth"
    ActiveWorkbook.Names("WoodyMinWidth").Delete
    Range("WoodyPriceChange").Name = NewName2 & "PriceChange"
    ActiveWorkbook.Names("WoodyPriceChange").Delete
    Range("WoodyRepeatedEnterpriseBonus").Name = NewName2 & "RepeatedEnterpriseBonus"
    ActiveWorkbook.Names("WoodyRepeatedEnterpriseBonus ").Delete
    Range("WoodyRepeatedMaxBonus").Name = NewName2 & "RepeatedMaxBonus"
    ActiveWorkbook.Names("WoodyRepeatedMaxBonus").Dele te
    Range("WoodyRepeatedMinBonus").Name = NewName2 & "RepeatedMinBonus"
    ActiveWorkbook.Names("WoodyRepeatedMinBonus").Dele te
    Range("WoodyRepeatedTypeBonus").Name = NewName2 & "RepeatedTypeBonus"
    ActiveWorkbook.Names("WoodyRepeatedTypeBonus").Del ete
    Range("WoodyShortName").Name = NewName2 & "ShortName"
    ActiveWorkbook.Names("WoodyShortName").Delete
    Range("WoodyType").Name = NewName2 & "Type"
    ActiveWorkbook.Names("WoodyType").Delete
    Range("WoodyYieldChange").Name = NewName2 & "YieldChange"
    ActiveWorkbook.Names("WoodyYieldChange").Delete
    Range("WoodyHerbyCompFactor").Name = NewName2 & "HerbyCompFactor"
    ActiveWorkbook.Names("WoodyHerbyCompFactor").Delet e
    Range("WoodyHerbyCompWidth").Name = NewName2 & "HerbyCompWidth"
    ActiveWorkbook.Names("WoodyHerbyCompWidth").Delete
    Range("WoodyHerbyShelterFactor").Name = NewName2 & "HerbyShelterFactor"
    ActiveWorkbook.Names("WoodyHerbyShelterFactor").De lete
    Range("WoodyHerbyShelterWidth").Name = NewName2 & "HerbyShelterWidth"
    ActiveWorkbook.Names("WoodyHerbyShelterWidth").Del ete
    Range("WoodyWoodyBuffer").Name = NewName2 & "WoodyBuffer"
    ActiveWorkbook.Names("WoodyWoodyBuffer").Delete

    'Fix up the name of the appearance rectangle
    ActiveSheet.Shapes(("WoodyAppearance")).Select
    Selection.Name = NewName2 & "Appearance"

    'Copy and paste as values
    Range(Cells(3, 13), Cells(8, 216)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

    'Delete the rest of the calculstions
    Range(Cells(9, 12), Cells(240, 215)).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp

    'Add labels
    Cells(3, 12) = "Returns"
    Cells(4, 12) = "Fixed Costs"
    Cells(5, 12) = "Variable Costs"
    Cells(6, 12) = "Woody Woody Spatial Costs"
    Cells(7, 12) = "Woody Herby Spatial Costs"
    Cells(8, 12) = "Height"
    Cells(1, 1).Select
    ActiveSheet.Name = NewName

    'Update the Woody Records sheet
    Sheets(WoodyRecords).Select
    If Cells(2, 1) = "" Then
    'First Woody record
    RecordNo = 1
    Else
    'Find the last record entered
    Cells(1, 1).Select
    Selection.End(xlDown).Select
    RecordNo = Selection.Row
    End If

    'Fill in the Woody Records Sheet
    Cells(RecordNo + 1, 1) = NewName

    'Update the Enterprise data on the layout sheet
    Sheets(LayoutSheet).Select
    If Range("FirstEnterprise") = "" Then
    CurrentRow = Range("FirstEnterprise").Row
    CurrentColumn = Range("FirstEnterprise").Column
    Else
    Range("EnterpriseLabel").Select
    Selection.End(xlDown).Select
    CurrentRow = Selection.Row + 1
    CurrentColumn = Selection.Column
    End If
    Cells(CurrentRow, CurrentColumn) = NewName
    Range("FirstEnterprise", Cells(CurrentRow, CurrentColumn)).Select
    Selection.Sort Key1:=Range("FirstEnterprise"), Order1:=xlAscending, Header:=xlNo _
    , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


    UpdateLists

    Application.ScreenUpdating = True

    End Sub

    Sub UpdateLists()
    Dim RowNum1 As Integer, RowNum2 As Integer, ColNum As Integer, ListArea As String

    Range("FirstEnterprise").Select
    RowNum1 = Selection.Row
    RowNum2 = RowNum1
    ColNum = Selection.Column
    While Cells(RowNum2, ColNum) <> ""
    RowNum2 = RowNum2 + 1
    Wend
    RowNum2 = RowNum2 - 1
    ListArea = "=R" & RowNum1 & "C" & ColNum & ":R" & RowNum2 & "C" & ColNum
    Range("CropToPlant").Select
    With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=ListArea
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "Invalid crop name"
    .InputMessage = ""
    .ErrorMessage = "You must pick one of the crops from the list"
    .ShowInput = True
    .ShowError = True
    End With

    End Sub

  4. #4
    New Lounger
    Join Date
    Mar 2002
    Location
    Perth, Western Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet becomes big and slow (Excel 2000 SR 1a)

    Another interesting thing I have found with turning screen updating off is that in some circumstances it can cause Excel to crash. A different routine included in this spreadsheet, but run at a different time draws rectangles on the screen in sizes, colours and postions depending on data on the spreadsheet. This routine was running well, except at times Dr Watson would come along and tell me that Excel had quit, I worked out that this occurred when large numbers of rectangles were drawn with the screen updating off. To rectify the problem I had to intermittently turn screen updating on and off within the routine, e.g. after every ten rectangles drawn allow a screen update. I've never seen this happen with turning off screen updating and then just processing data and suspect it is caused by some sort of buffer overrun, but it is something to be aware of. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

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

    Re: Sheet becomes big and slow (Excel 2000 SR 1a)

    Without having your workbook, it is just about impossible to look at this much code and analyze it, change it, and debug the changes. However, a quick look at this code shows that it is constantly activating different workbooks, different worksheets, and selecting different ranges of cells. This will cause your code to run very slowly. You need to try to eliminate all .Activate and .Select methods.

    As an example, you have this code near the beginning of the code:

    <pre> If EnterpriseType = "Woody" Then
    Range(Cells(1, ColSel - 1), Cells(194, ColSel + 9)).Copy
    Windows(ImagineWorkbook).Activate
    Sheets("Woody Template").Select
    Else
    Range(Cells(1, ColSel - 1), Cells(152, ColSel + 9)).Copy
    Windows(ImagineWorkbook).Activate
    Sheets("Herby Template").Select
    End If
    Cells(1, 1).Select
    ActiveSheet.Paste
    Windows(DatabaseWorkBook).Activate
    </pre>


    You should be able to replace this code with the code below which should do the same thing (I could not check it for bugs without your workbook), and should run much faster without the .Activate and .Select methods:

    <pre> If EnterpriseType = "Woody" Then
    Windows(DatabaseWorkBook).Sheets("Sheet1").Range(C ells(1, ColSel - 1), Cells(194, ColSel + 9)).Copy
    Else
    Windows(DatabaseWorkBook).Sheets("Sheet1").Range(C ells(1, ColSel - 1), Cells(152, ColSel + 9)).Copy
    End If
    Windows(ImagineWorkbook).Paste Destination:=Windows(ImagineWorkbook).Sheets(Templ ate).Cells(1, 1)
    </pre>


    The above code assumes that the sheet name in the DatabaseWorkBook is named "Sheet1" which I could not figure out from the code.

    That code could be further speeded up by using object variables to reference the diferent sheets like this:

    <pre>Dim oDBSheet As Worksheet, oIMSheet As Worksheet
    Set oDBSheet = Windows(DatabaseWorkBook).Worksheets("Sheet1")
    Set oIMSheet = Windows(ImagineWorkbook).Sheets(Template)
    If EnterpriseType = "Woody" Then
    oDBSheet.Range(Cells(1, ColSel - 1), Cells(194, ColSel + 9)).Copy
    Else
    oDBSheet.Range(Cells(1, ColSel - 1), Cells(152, ColSel + 9)).Copy
    End If
    Windows(ImagineWorkbook).Paste Destination:=oIMSheet.Cells(1, 1)
    </pre>

    Legare Coleman

  6. #6
    New Lounger
    Join Date
    Mar 2002
    Location
    Perth, Western Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet becomes big and slow (Excel 2000 SR 1a)

    Thankyou for your response Legare,

    Good tips to tidy some of my sloppy techniques.

    I suspect that my most likely cause of solution here is to radically change the way I'm doing things and hope that the problem just goes away.

    I'm probably not framing my problem all that well, but I'll try and add a little more info.

    The speed that the macro runs when I run it for the first time, a few seconds enough to adjust my posture, but not to get out of my chair is ok. However, each time it is run it gets slower and after about four runs it takes about ten minutes. Not only does the macro slow down, but the spreadsheet becomes slow even when the macro is not running (this is the most annoying part), but not on all sheets or even looking at all cells on the slow sheets. The sheets that are affected are those that the data is pasted onto and the copies of this sheet that become the new data sheets. The size of the sheet that has the data pasted onto it also increases when the macro is run, which doesn't make sense to me because I'm just pasting new data over old data (I guess the size of the sheet by deleting it and saving the workbook under a different name and looking at the properties).

    I appreciate the advice I've been given, both good tips for improving my code, but recognise that this is probably almost impossible to solve without access to the whole spreadsheet and probably difficult even then. The time has come for major renovations!

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet becomes big and slow (Excel 2000 SR 1a)

    Another thing that springs to mind, is that I get the impression your book contains lots of defined names. Although XL help says the number of named ranges is limited by available memory, I would have a look whether you really need all the named ranges If the names are just there for use in the VBA code processing, it might be better to set the ranges to object variables:

    DIM rWoodyRange1 as Range
    Set rWoodyRange1=Thisworkbook.worksheets("Sheet1").[A1:C3]

    Then if you need to copy this range later on, the code is simply:
    rWoodyRange1.Copy
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    New Lounger
    Join Date
    Mar 2002
    Location
    Perth, Western Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet becomes big and slow (Excel 2000 SR 1a)

    Thanks Jan,

    the sheet currently has about 370 names, but this number changes whenever datasets are added or removed. I'm not sure if this number of names is large enough for concern. If it is I'm probably in real trouble because once this slow down problem is solved I aim to probably triple the number of names by adding several more datasets. Probably another rethink required on my part.

    Don

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet becomes big and slow (Excel 2000 SR 1a)

    Why not try (with a copy !!!!!!!!) deleting all (most) names:

    Sub DelNames()
    Dim oName as object
    For Each oName in Activeworkbook.names
    if instr(oname.name,"Whatever")=0 then
    ' delete names that do not contain "Whatever" (case
    ' sensitive)
    oName.Delete
    End if
    Next
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Sheet becomes big and slow (Excel 2000 SR 1a)

    If tou go to Tools/Options and click on th calculation tab and set Calculation to Manual, is everything slow?
    Legare Coleman

  11. #11
    New Lounger
    Join Date
    Mar 2002
    Location
    Perth, Western Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet becomes big and slow (Excel 2000 SR 1a)

    Thanks Legare,

    I've tried that and it appears to make no difference.

  12. #12
    New Lounger
    Join Date
    Mar 2002
    Location
    Perth, Western Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet becomes big and slow (Excel 2000 SR 1a)

    Thanks for the advice Jan,

    I attacked a version of the spreadsheet that has become slow. I deleted all of the names, but alas the problem has not been solved. It still grinds to a halt in some places and behaves normally in others.
    I see the problem as being more of curiosity value at the moment because looking back I see that I have done several things poorly. Therefore, this hangup is a driver for me to go back and change quite a few things including the way that I'm transferring and storing the data in this workbook. Hopefully when I reach this point of development again the problem will not recur, but if it does I'll be attaching the sad faced smily for future Emails.

    Don <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sheet becomes big and slow (Excel 2000 SR 1a)

    Don, I'm out of good ideas, but here's something simple. There is so much copy & paste activity in the code that I wonder, after you have run your code, and the WB is huge and slow, have you used End-Home to examine if there are formatted cells, and possibly formulas with no valid references, using worksheet cell ranges that they no longer need to use? If so, you may want to add
    Worksheet(reference_or_name).Range(no_longer_used_ range).Clear
    at periodic points through the code and as a final cleanup.
    -John ... I float in liquid gardens
    UTC -7DS

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet becomes big and slow (Excel 2000 SR 1a)

    Building laterally on John BF's observation, something that MIGHT work is clearing the clipboard between copy and paste operations. This can be just a simple CleanClipboard Sub that you can call between each Copy/Paste such as:

    Sub CleanClipboard()
    Range("IV1").Select
    Range("IV1").Copy Destination:= Range("IV1").Paste
    End Sub

    HTH
    Gre

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

    Re: Sheet becomes big and slow (Excel 2000 SR 1a)

    I'm not sure exactly what this code is doing, but is that first like to select the range necessary? Changing the selection is always very slow, and it does not look like it is doing anything.
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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