Code devours system resources and causes excel to lag.

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,077
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.


Code:
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
There looks nothing in that code that would cause excel to crash. Id create a new workbook and transfer the data over.
 
Upvote 0
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.
 
Upvote 0
I guess I’ll try to reset the used range on a few sheets and see if that makes things better.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top