Streamlining or speeding up some VBA code - copying data from userform to worksheet

[richard_a]

Hi All,

I have some code that copies data from a userform into a table. The code works but it's a little slow and clunky. I wondered if anyone could come up with a faster / more efficient code? I'm a VBA beginner and so any help would be greatly appreciated.

Many thanks,

Richard

Private Sub CommandButton1_Click()

Dim i As Integer
Dim LASTROW As Long
LASTROW = ActiveWorkbook.Worksheets("Data Table").ListObjects(1).ListRows.Count

For i = 1 To 30
If Controls("ComboBox" & i).Value <> "" Then

ActiveWorkbook.Worksheets("Data Table").ListObjects(1).DataBodyRange(LASTROW + i, 1).Value = "UniqueID"
ActiveWorkbook.Worksheets("Data Table").ListObjects(1).DataBodyRange(LASTROW + i, 2).Value = CDate(DateBox.Value)
ActiveWorkbook.Worksheets("Data Table").ListObjects(1).DataBodyRange(LASTROW + i, 3).Value = txtbox1.Value
ActiveWorkbook.Worksheets("Data Table").ListObjects(1).DataBodyRange(LASTROW + i, 4).Value = txtbox2.Value
ActiveWorkbook.Worksheets("Data Table").ListObjects(1).DataBodyRange(LASTROW + i, 5).Value = txtbox3.Value
ActiveWorkbook.Worksheets("Data Table").ListObjects(1).DataBodyRange(LASTROW + i, 6).Value = txtbox4.Value
ActiveWorkbook.Worksheets("Data Table").ListObjects(1).DataBodyRange(LASTROW + i, 7).Value = Controls("Combobox" & i).Value
ActiveWorkbook.Worksheets("Data Table").ListObjects(1).DataBodyRange(LASTROW + i, 8).Value = txtbox5.Value
ActiveWorkbook.Worksheets("Data Table").ListObjects(1).DataBodyRange(LASTROW + i, 9).Value = txtbox6.Value
 End If
    Next i
    
For i = 2 To 30
If Controls("ComboBox" & i).Value <> "" Then
ActiveWorkbook.Worksheets("Data Table").ListObjects(1).DataBodyRange(LASTROW + i, 10).Value = Controls("StatusBox" & i).Value
ActiveWorkbook.Worksheets("Data Table").ListObjects(1).DataBodyRange(LASTROW + i, 11).Value = Controls("NameBox" & i).Value
ActiveWorkbook.Worksheets("Data Table").ListObjects(1).DataBodyRange(LASTROW + i, 12).Value = Controls("ScoreBox" & i).Value
 End If
    Next i
  
    
    MsgBox "Data has been added"
    End Sub

 

[shg]

Maybe ...

Sub richard()
  Dim i             As Long
  Dim LASTROW       As Long
  Dim oLO           As ListObject
  Dim r             As Range

  LASTROW = ActiveWorkbook.Worksheets("Data Table").ListObjects(1).ListRows.Count

  Set oLO = Worksheets("Data Table").ListObjects(1)
  Set r = oLO.DataBodyRange.Rows(LASTROW)

  For i = 1 To 30
    If Len(Controls("ComboBox" & i).Value) Then
      r.Offset(i).Range("A1:L1").Value = Array("UniqueID", CDate(DateBox.Value), txtbox1.Value, txtbox2.Value, txtbox3.Value, _
                                               txtbox4.Value, Controls("Combobox" & i).Value, txtbox5.Value, txtbox6.Value, _
                                               Controls("StatusBox" & i).Value, Controls("NameBox" & i).Value, Controls("ScoreBox" & i).Value)
    End If
  Next i

  MsgBox "Data has been added"
End Sub