saving fresh data!

[asyamonique]

Hello,
Is there any way to speed up running below code?
When the user sends those datas it takes time to complite progress.
Thank you.

Private Sub CommandButton2_Click()

Application.ScreenUpdating = False


Dim asya As Long
With Sheets("list")
If .Cells(1, 1).Value = "" Then
asya = 1
Else
asya = .Cells(Rows.Count, 1).End(xlUp).Row + 1
End If


.Cells(asya, 1) = ComboBox65.Value
.Cells(asya, 2) = ComboBox66.Value
.Cells(asya, 3) = ComboBox67.Value
.Cells(asya, 4) = ComboBox34.Value
.Cells(asya, 5) = ComboBox35.Value
.Cells(asya, 6) = ComboBox36.Value
.Cells(asya, 7) = ComboBox37.Value
.Cells(asya, 8) = ComboBox38.Value
.Cells(asya, 9) = ComboBox39.Value
.Cells(asya, 10) = ComboBox40.Value
.Cells(asya, 11) = ComboBox41.Value
.Cells(asya, 12) = ComboBox42.Value
.Cells(asya, 13) = ComboBox43.Value
.Cells(asya, 14) = ComboBox44.Value
.Cells(asya, 15) = ComboBox45.Value
.Cells(asya, 16) = ComboBox46.Value
.Cells(asya, 17) = ComboBox47.Value
.Cells(asya, 18) = ComboBox48.Value
.Cells(asya, 19) = ComboBox49.Value
.Cells(asya, 20) = ComboBox50.Value
.Cells(asya, 21) = ComboBox51.Value
.Cells(asya, 22) = ComboBox52.Value
.Cells(asya, 23) = ComboBox53.Value
.Cells(asya, 24) = ComboBox54.Value
.Cells(asya, 25) = ComboBox55.Value
.Cells(asya, 26) = ComboBox56.Value
.Cells(asya, 27) = ComboBox57.Value
.Cells(asya, 28) = ComboBox58.Value
.Cells(asya, 29) = ComboBox59.Value
.Cells(asya, 30) = ComboBox60.Value
.Cells(asya, 31) = ComboBox61.Value
.Cells(asya, 32) = ComboBox62.Value
.Cells(asya, 33) = ComboBox63.Value
.Cells(asya, 34) = ComboBox64.Value
.Cells(asya, 35) = TextBox2.Value


End With


ComboBox34 = ("")
ComboBox35 = ("")
ComboBox36 = ("")
ComboBox37 = ("")
ComboBox38 = ("")
ComboBox39 = ("")
ComboBox40 = ("")
ComboBox41 = ("")
ComboBox42 = ("")
ComboBox43 = ("")
ComboBox44 = ("")
ComboBox45 = ("")
ComboBox46 = ("")
ComboBox47 = ("")
ComboBox48 = ("")
ComboBox49 = ("")
ComboBox50 = ("")
ComboBox51 = ("")
ComboBox52 = ("")
ComboBox53 = ("")
ComboBox54 = ("")
ComboBox55 = ("")
ComboBox56 = ("")
ComboBox57 = ("")
ComboBox58 = ("")
ComboBox59 = ("")
ComboBox60 = ("")
ComboBox61 = ("")
ComboBox62 = ("")
ComboBox63 = ("")
ComboBox64 = ("")
ComboBox67 = ("")


Application.ScreenUpdating = True


End Sub

 

[Yongle]

Test in a copy of your workbook
- it works with my tst data, but I do not know if it will work for you

- Single string (splitStr) delimited by "|" created from combobox & textbox values
- splitStr is built up in sections because there is a limit to the number of line continuations
- splitStr is split and individual values written to the worksheet
- line below converts numbers formatted as text back to numbers

    .Cells(asya, 1).Resize(, 35).Value = .Cells(asya, 1).Resize(, 35).Value

- this code should be faster because all values are written at the same time to the workbook

Private Sub CommandButton2_Click()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    Const [COLOR=#ff0000]Z[/COLOR] = "[COLOR=#ff0000]|[/COLOR]"
    Dim splitStr As String
    Dim asya As Long
    With Sheets("list")
        If .Cells(1, 1).Value = "" Then
            asya = 1
        Else
            asya = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    End If

    splitStr = splitStr & _
            ComboBox65.Value & Z & _
            ComboBox66.Value & Z & _
            ComboBox67.Value & Z & _
            ComboBox34.Value & Z & _
            ComboBox35.Value & Z & _
            ComboBox36.Value & Z & _
            ComboBox37.Value & Z & _
            ComboBox38.Value & Z & _
            ComboBox39.Value & Z & _
            ComboBox40.Value
    splitStr = splitStr & Z & _
            ComboBox41.Value & Z & _
            ComboBox42.Value & Z & _
            ComboBox43.Value & Z & _
            ComboBox44.Value & Z & _
            ComboBox45.Value & Z & _
            ComboBox46.Value & Z & _
            ComboBox47.Value & Z & _
            ComboBox48.Value & Z & _
            ComboBox49.Value & Z & _
            ComboBox50.Value
    splitStr = splitStr & Z & _
            ComboBox51.Value & Z & _
            ComboBox52.Value & Z & _
            ComboBox53.Value & Z & _
            ComboBox54.Value & Z & _
            ComboBox55.Value & Z & _
            ComboBox56.Value & Z & _
            ComboBox57.Value & Z & _
            ComboBox58.Value & Z & _
            ComboBox59.Value & Z & _
            ComboBox60.Value
    splitStr = splitStr & Z & _
            ComboBox61.Value & Z & _
            ComboBox62.Value & Z & _
            ComboBox63.Value & Z & _
            ComboBox64.Value & Z & _
            TextBox2.Value
    .Cells(asya, 1).Resize(, 35) = Split(splitStr, "|")
    .Cells(asya, 1).Resize(, 35).Value = .Cells(asya, 1).Resize(, 35).Value
End With


    ComboBox34 = ("")
    ComboBox35 = ("")
    ComboBox36 = ("")
    ComboBox37 = ("")
    ComboBox38 = ("")
    ComboBox39 = ("")
    ComboBox40 = ("")
    ComboBox41 = ("")
    ComboBox42 = ("")
    ComboBox43 = ("")
    ComboBox44 = ("")
    ComboBox45 = ("")
    ComboBox46 = ("")
    ComboBox47 = ("")
    ComboBox48 = ("")
    ComboBox49 = ("")
    ComboBox50 = ("")
    ComboBox51 = ("")
    ComboBox52 = ("")
    ComboBox53 = ("")
    ComboBox54 = ("")
    ComboBox55 = ("")
    ComboBox56 = ("")
    ComboBox57 = ("")
    ComboBox58 = ("")
    ComboBox59 = ("")
    ComboBox60 = ("")
    ComboBox61 = ("")
    ComboBox62 = ("")
    ComboBox63 = ("")
    ComboBox64 = ("")
    ComboBox67 = ("")
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[asyamonique]

That's great
Many thanks.

 

[Yongle]

Thanks for the feedback