CODEWITHSUNDEEP
performancevbaexcel
Hareborn
Hareborn

Reputation: 175

Very slow removing command buttons and rows from sheet

This sub removes all command buttons and their associated rows that were created programmatically on a sheet. It takes around 20 seconds to remove 60 command buttons and rows. I've stepped through it and can find no problems with it.

Sub ResetForm_Click()

    Dim Contr
    Dim controlname As String
    Dim WS As Worksheet

    Set WS = ActiveSheet

        For Each Contr In WS.OLEObjects

        controlname = Mid(Contr.Name, 1, 2)

            If controlname = "CB" Then
                Contr.TopLeftCell.Rows.EntireRow.Delete
                Contr.Delete
            End If

        Next

End Sub

Upvotes: 1

Views: 55

Answers (2)

BruceWayne
BruceWayne

Reputation: 23283

Without editing your macro too much, turn off screen updating and autocalculation, see if that helps.

At the beginning of your macro (I usually put it after my declarations), add

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Then at the end (before End Sub), turn them back on

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166331

Instead of deleting rows one-by-one try something like this (untested):

Sub ResetForm_Click()

    Dim Contr
    Dim controlname As String
    Dim WS As Worksheet, rDel As Range

    Set WS = ActiveSheet

        For Each Contr In WS.OLEObjects

        controlname = Mid(Contr.Name, 1, 2)

            If controlname = "CB" Then
                If rDel Is Nothing then
                    Set rDel = Contr.TopLeftCell
                Else
                    Set rDel = Application.Union(rDel, Contr.TopLeftCell)
                End If 
                Contr.Delete
            End If

        Next

        If Not rDel Is Nothing then rDel.EntireRow.Delete 

End Sub

Upvotes: 4

Related Questions