How to speed up a macro

[kelly mort]

Hello All,
I have this code and I wanna ask if there is a way to speed it up. It takes longer than I expected to display the msgbox after the next i. thanks
kelly

Private Sub CmdAdd_Click()
    Dim ans As String, anss As Date
    sht = CmbClass.Value
    On Error GoTo errHandler
    If Rw2.Text = "" Or Rw5.Text = "" Then
    MsgBox "Fields empty." & vbCrLf & _
    "Fill the fields:" , _
    vbInformation, "Blank Fields Alert"
    Exit Sub
    Else

    If WorksheetFunction.CountIf(Sheets(sht).Range("C7:C110"), Me.Rw2.Text) > 0 Then
    MsgBox "Duplicate name alert"
    Exit Sub
    End If
    End If
    
    With Sheets(sht)
    If MsgBox("are you sure?” _
, vbYesNo + vbDefaultButton2 + vbQuestion, "Add this data?") = vbNo Then Exit Sub
    Application.ScreenUpdating = False
    Set Drng = .Range("B7")
    Set lrRng = .Cells(.Rows.Count, Drng.Column).End(xlUp).Offset(1, 0)
    lrRng.Value = Application.Max(Drng.EntireColumn) + 1
    lrRng.Offset(0, 1).Value = Rw2.Value
    lrRng.Offset(0, 2).Value = Rw3.Value
    lrRng.Offset(0, 3).Value = Rw4.Value
    lrRng.Offset(0, 4).Value = CDate(Rw5.Value)
    
    For i = 5 To 22
        lrRng.Offset(0, i).Value = Controls("Rw" & i + 1).Value
    Next i
    MsgBox "Data sent successfully"  _
, vbInformation, "Data added "
    
    SortIt
    Rw1.Value = ""
    Rw2.Value = ""
    Rw3.Value = ""
    For i = 5 To 23
    Controls("Rw" & i).Value = ""
    Next i
    End With
    
    Nroll.Text = Sheet2.Range("C12").Text
    NrollOption.Text = Sheet2.Range("H11").Text
    CmdNext.Enabled = False
    CmdBack.Enabled = False
    CmdPrintThis.Enabled = False
    CmdPrintMore.Enabled = True
    Application.ScreenUpdating = True
    On Error GoTo 0
    Exit Sub
errHandler::
    MsgBox "There was an error", vbInformation, "Error Alert"
End Sub

 

[tonyyy]

Application.Calculation = xlCalculationManual

Application.Calculation = xlCalculationAutomatic

The first line will turn calculations off; the second line will turn them on.

 

[kelly mort]

Yes!!!!!. Very fast. 00:00.4 is the time used before was 00:03.8
Thanks and will report when i have another issue thanks
Kelly

 

[tonyyy]

You're very welcome...