Code devours system resources and causes excel to lag.

[andrewb90]

Hello all,

I have this code that takes info from a cell and puts it in comments, but when it does, excel always needs to restart as a result of the systems resources getting used it and causing major lag. I was wondering if anybody could give me insight into what exactly causes this, and if there is a cure.

Private Sub CellToComment()'Updateby20140509
'Note: Will not add comment if blank, will clear comment if notes deleted
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
Sheets("Scheduler").Select
ActiveSheet.Unprotect "majinbuu"
'xTitleId = "KutoolsforExcel"
'Set NoteRng = Range("S89:S100").SpecialCells(xlCellTypeVisible)
Set CmtRng = Sheets("Scheduler").Range("D89:D207").SpecialCells(xlCellTypeVisible)
'Set WorkRng = Application.Selection
'Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In CmtRng 'this is where it's gonna print
    'Rng.NoteText Text:=Rng.Value
    'Rng.NoteText Text:=Sheets("Master Availability").Range("S" & Rng.Row)
    If Sheets("Master Availability").Range("S" & Rng.Row) = "" Then
        Sheets("Scheduler").Range("D" & Rng.Row).ClearComments
    Else
        Rng.NoteText Text:=Sheets("Master Availability").Range("D" & Rng.Row) & ":" & vbNewLine & Sheets("Master Availability").Range("S" & Rng.Row)
    End If
    '.Offset(rowOffset:=0, columnOffset:=-1)
Next
End Sub

Thanks,

Andrew

 

[andrewb90]

bump^

 

[andrewb90]

anybody have any ideas?

 

[steve the fish]

There looks nothing in that code that would cause excel to crash. Id create a new workbook and transfer the data over.

 

[andrewb90]

My workbook is too big/complex to do that

 

[steve the fish]

There is nothing in your code that should hog the memory. Go through your workbook. Check the usedrange in each sheet (CTRL-END). These can be draining. Ive seen spreadsheets where every cell is in the usedrange. They become unusable. Aside from that id rebuild or even redesign.

 

[andrewb90]

I guess I’ll try to reset the used range on a few sheets and see if that makes things better.

 

[andrewb90]

I reset the used range, and that didn't seem to solve it.

Does anybody know if there's a way to reset the sheet without closing and reopening? Perhaps the excel equivalent of clearing the cache?

To add more detail: The screen flickers as if a short run of code run with screen updating were still on, every time I double click a cell.
If I run other macros, the result is still the same. And I have a selection change on the sheet that highlights rows, and it is unaffected until I double click.
On other sheets, if I double click a cell, the images become distorted, and excel almost freezes up. Yet there's never any long lasting damage, because closing and reopening excel eliminates this issue.