How to improve the saving speed for userform in ex

问题:

This is the userform created by me. Then ,it is used as an input platform .There are some different tables ,eg.2016 ,2017.... The logic of that save button is to search the Year(Date) that user input and location the right worksheet. Then , it will find the last row of that worksheet .

For example , the last row is row 1000. The first row of the userform will save on row 1001.The second row of the userform will save on row 1002....

Question

However , when i test in the real excel file , the saving speed is too slow .As the real excel file is large (Around 1XXXX rows in every worksheet) .It uses 8 sec to save one row for userform , 13sec to save two row .

Obviously , the saving speed is unacceptable . Any method can improve it ?

  If ComboBox3.Value = "2016" Then
    Worksheets("2016").Activate
    j = WorksheetFunction.CountA(Worksheets("2016").Range("A:A")) + 1
    End If

    If ComboBox3.Value = "2017" Then
    Worksheets("2017").Activate
    j = WorksheetFunction.CountA(Worksheets("2017").Range("A:A")) + 1
    End If



    '1st



    If ComboBox4.Value = "" Then
    Else
    Cells(j, 1) = ComboBox434.Value
    Cells(j, 5) = ComboBox1.Value
    Cells(j, 4) = ComboBox2.Value
    Cells(j, 3) = ComboBox3.Value
    If ComboBox4.ListIndex <> -1 Then
    Cells(j, 6) = TimeValue(ComboBox4.Value & ":" & ComboBox5.Value)
    Cells(j, 24) = ComboBox4.Value
    Cells(j, 25) = ComboBox5.Value
    Else
    Cells(j, 6) = ""
    End If
    Cells(j, 7) = ComboBox6.Value
    Cells(j, 8) = ComboBox7.Value
    Cells(j, 9) = ComboBox8.Value
    Cells(j, 10) = TextBox2.Value
    Cells(j, 11) = TextBox3.Value
    Cells(j, 12) = TextBox4.Value
    Cells(j, 13) = TextBox5.Value
    Cells(j, 14) = TextBox42.Value
    Cells(j, 15) = TextBox43.Value
    Cells(j, 16) = TextBox44.Value
    Cells(j, 17) = TextBox666.Value

    'If ComboBox4.Value = "" Then

    End If

    '2nd

    j = j + 1

    If ComboBox9.Value = "" Then
    Else
    Cells(j, 1) = ComboBox434.Value
    Cells(j, 5) = ComboBox1.Value
    Cells(j, 4) = ComboBox2.Value
    Cells(j, 3) = ComboBox3.Value
    If ComboBox9.ListIndex <> -1 Then
    Cells(j, 6) = TimeValue(ComboBox9.Value & ":" & ComboBox10.Value)
    Cells(j, 24) = ComboBox9.Value
    Cells(j, 25) = ComboBox10.Value
    Else
    Cells(j, 6) = ""
    End If
    Cells(j, 7) = ComboBox11.Value
    Cells(j, 8) = ComboBox12.Value
    Cells(j, 9) = ComboBox13.Value
    Cells(j, 10) = TextBox6.Value
    Cells(j, 11) = TextBox7.Value
    Cells(j, 12) = TextBox8.Value
    Cells(j, 13) = TextBox9.Value
    Cells(j, 14) = TextBox45.Value
    Cells(j, 15) = TextBox46.Value
    Cells(j, 16) = TextBox47.Value
    Cells(j, 17) = TextBox617.Value

回答1:

You can probably save some time by switching calculation to manual and then calculate after the information has been inserted.

Specifically, at the start of your code:

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

and again at the end of your code:

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

In addition, you might want to store your data in a variant array, which can be inserted into the worksheet all at one go, or an array and resizing Cells(j,1) to the size of the array

e.g.

Cells(j, 1).resize(Ubound(arr,1), Ubound(arr,2)) = arr 'Need to check for exact syntax

This could minimize the number of times the worksheets are hit.