Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("Course Bookings").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtName.Value
ActiveCell.Offset(0, 1) = txtPhone.Value
ActiveCell.Offset(0, 2) = cboDepartment.Value
ActiveCell.Offset(0, 3) = cboCourse.Value
If optIntroduction = True Then
VBA adding value to last available row in excel sheet
Article by Mukesh Dak
Mukesh has written 253 awesome articles for us.
If you liked this post, you will definitely enjoy our others. Subscribe to the feed via RSS or EMAIL to get instantly updated for those awesome posts soon to come.
{ 1 comment… read it below or add one }
This is much more complicated than it needs to be. Instead of looping to find the next empty cell in a column, you can access it directly:
ActiveWorkbook.Sheets(“Course Bookings”).Activate
With Range("A" & Rows.Count).End(xlUp).Offset(1,0)
.Value = txtName.Value
.Offset(0, 1) = txtPhone.Value
.Offset(0, 2) = cboDepartment.Value
.Offset(0, 3) = cboCourse.Value
End With
Or write all the values in one shot, using an array:
Dim rng As Excel.Range
Dim valuestoWrite() As Variant
ActiveWorkbook.Sheets("Course Bookings").Activate
Set rng = ActiveSheet.Range(Rows.Count).End(xlUp).Offset(1, 0)
valuestoWrite = Array(txtName.Value, txtPhone.Value, cboDepartment.Value, cboCourse.Value)
Range(rng, rng.Offset(0, 3)).Value = valuestoWrite