Slow, jerky behaviour with a blank sheet

[abilsborough]

I have quite a complicated but reasonably small Excel 2010 spreadsheet which uses VBA to copy rows from one sheet to another, downloads internet stock prices and has a fair bit of conditional formatting. After a session of testing (using VBA to copy rows backwards and forwards, creating, deleting sheets etc.) one particular sheet (and its copies) slowed down dramatically. The sheet was very slow when moving from cell to cell, scrolling, typing, as well as saving and copying the sheet. All other sheets in the workbook are OK.


In trying to isolate the problem, I copied the offending sheet to a new spreadsheet, saved it as xlsx instead of the original xlsm and deleted all content, formatting, VBA code and AddIns. However even after all that the sheet still is a bit slow and jerky (although not quite as slow). Again the other sheets in the workbook are perfectly fine. Any ideas what the problem could be? Without a solution it looks like my next step is to completely rebuild the spreadsheet which would be a major pain and the same problem may occur again.


BTW I'm not sure if it's relevant but when I do Ctrl+End the AO:1 cell is selected even after I delete all the rows and columns.


TIA, Alan

 

[ZVI]

Hi,

See the reason of this behavior in the link below.
On slow sheet press F5 – Special… – Objects – OK to select invisible (zero size) Rectangles objects and then delete them.
If it's too slow then do as it was suggested in this link: Only one sheet very slow

Regards

 

[abilsborough]

Perfect! Thanks for the help.

I had no less than 27,000 shapes in my sheet and I deleted them via VBA, the sheet is now back to normal. How I managed to get 27,000 shapes in just a few short hours of testing is anyone's guess. It goes without saying I'll be keeping my eye on the number of shapes accumulated from now on. My Ctrl+End now goes to A:1.

Your help is most appreciated.

 

[ZVI]

Nice that it's helped!
Most frequently the source of the issue is in dragging images from web-pages.
Or in copying images from an external source and paste it into Excel.
Sometimes this creates zero-size rectangles placed over cells.
If those cells are copied then rectangles are copied too with doubling of its count like in Wheat and chessboard problem

 

[debeast]

Hi,

See the reason of this behavior in the link below.
On slow sheet press F5 – Special… – Objects – OK to select invisible (zero size) Rectangles objects and then delete them.
If it's too slow then do as it was suggested in this link: Only one sheet very slow

Regards

Thank you Sir!
Removing the special objects did the work!

 

[footoo]

Here's a macro to count the buttons on a worksheet:

Sub Buttons_Count()
'Counts Forms Control buttons only


Dim activeSht As Worksheet, b#, shp, i#, x#, dup%
Set activeSht = ActiveSheet
Application.ScreenUpdating = False
'Count buttons and put button names and captions in a temp sheet
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"
b = 0
For Each shp In activeSht.Shapes
      If shp.Type = 8 Then 'If it is a Forms Control
            If shp.FormControlType = 0 Then 'If it is a button
                  b = b + 1
                  With Sheets("Temp").Cells(Rows.Count, 1).End(xlUp)(2)
                        .Value = shp.Name
                        .Offset(0, 1).Value = shp.TextFrame.Characters.Text
                  End With
            End If
      End If
Next
'Sort by column A (button name)
ActiveWorkbook.Worksheets("Temp").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Temp").Sort.SortFields.Add Key:=[B1], _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Temp").Sort
    .SetRange Range("A:B")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
'Check for duplicate buttons and advise caption name of each duplicate button
x = 0
For i = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
       If Cells(i, 2).Value = Cells(i - 1, 2).Value Then
            dup = MsgBox("The button '" & Cells(i, 2) & "' is duplicated." _
                  & Chr(13) & Chr(13) & "Do you want to delete this duplicate?", vbYesNo)
            If dup = vbYes Then
                  activeSht.Shapes(Cells(i - 1, 1)).Delete
                  b = b - 1
            Else
                  x = x + 1
            End If
       End If
Next
'Select original sheet
activeSht.Select
'Delete the temp sheet
Application.DisplayAlerts = False
Sheets("Temp").Delete
Application.DisplayAlerts = True
'Advise how many buttons and how many duplicated buttons
If b = 1 Then
      MsgBox "There is one button on this sheet."
      Exit Sub
End If
If b = 0 Then
      MsgBox "There are no buttons on this sheet."
      Exit Sub
End If
If x = 0 Then
      MsgBox "There are " & b & " buttons on this sheet." _
            & Chr(13) & Chr(13) & "There are no duplicated buttons."
      Exit Sub
Else
      If x = 1 Then MsgBox "There are " & b & " buttons on this sheet." _
            & Chr(13) & Chr(13) & "There is one duplicated button."
      If x > 1 Then MsgBox "There are " & b & " buttons on this sheet." _
            & Chr(13) & Chr(13) & "There are " & x & " duplicated buttons."
End If


End Sub

And here's one to delete duplicate buttons:

Sub Buttons_Delete_Duplicates()
'Counts Forms Control buttons only


Dim activeSht As Worksheet, b#, shp, i#, x#, dup%
Set activeSht = ActiveSheet
Application.ScreenUpdating = False
'Count buttons and put button names and captions in a temp sheet
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"
b = 0
For Each shp In activeSht.Shapes
      If shp.Type = 8 Then 'If it is a Forms Control
            If shp.FormControlType = 0 Then 'If it is a button
                  b = b + 1
                  With Sheets("Temp").Cells(Rows.Count, 1).End(xlUp)(2)
                        .Value = shp.Name
                        .Offset(0, 1).Value = shp.TextFrame.Characters.Text
                  End With
            End If
      End If
Next
'Sort by column A (button name)
ActiveWorkbook.Worksheets("Temp").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Temp").Sort.SortFields.Add Key:=[B1], _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Temp").Sort
    .SetRange Range("A:B")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
'Delete duplicate buttons
For i = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
       If Cells(i, 2).Value = Cells(i - 1, 2).Value Then
                  activeSht.Shapes(Cells(i - 1, 1)).Delete
                  b = b - 1
       End If
Next
'Select original sheet
activeSht.Select
'Delete the temp sheet
Application.DisplayAlerts = False
Sheets("Temp").Delete
Application.DisplayAlerts = True
End Sub