Quicker way to clear a range of cells?

[niall91]

Hi guys

This code works but its as slow as a wet week. Could anyone help write this so it runs much quicker.
Thank you

Sub OptimizeVBA(isOn As Boolean)
    Application.Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic)
    Application.EnableEvents = Not (isOn)
    Application.ScreenUpdating = Not (isOn) 
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 trow = Target.Row
 OptimizeVBA True
Dim emp As Range
Set emp = Cells(trow, 13)

If Target.Column = 13 Then
    If IsEmpty(emp) = True Then
        With Sheets("Frame Sets")
        .Range(.Cells(trow, 18), .Cells(trow, 37)) = ""
        End With
    End If
End If
 
 OptimizeVBA False
End Sub

 

[jasonb75]

I can't see any reason for your code being noticeably slow, this is how I would have done the same thing but I would expect performance to be almost identical. I would expect that any major performance issues are caused by formulas calculating when the code finishes running.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim trow As Long
If Target.Column = 13 Then
    trow = Target.Row
    OptimizeVBA True
    If IsEmpty(Cells(trow, 13)) Then
        With Sheets("Frame Sets")
            .Range(.Cells(trow, 18), .Cells(trow, 37)).ClearContents
        End With
    End If
    OptimizeVBA False
End If
End Sub