Find Next Empty Row for Automatic Data
Entry in
an Excel Worksheet using a UserForm
Let's have a closer look at the new code for the command button of the improved user-form. Here we have assumed that we have header labels starting at cell address A4.
Private Sub cmdAddData_Click()
Dim RowCount As Long
Dim benefits, total As Single
If Me.txtName.Value = "" Then
MsgBox "Please enter a name", vbExclamation, "Employee Data"
Me.txtName.SetFocus
End If
'Range("A5") = txtName.Text
If Not IsNumeric(Me.txtSalary.Value) Then
MsgBox "The Amount box must contain a number.", vbExclamation, "Employee Data"
Me.txtSalary.SetFocus
End If
'Range("B5") = txtSalary.Value
benefits = txtSalary.Value * 0.5
'Range("C5") = benefits
total = txtSalary.Value + benefits
'Range("D5") = total
RowCount = Worksheets("Sheet1").Range("A4").CurrentRegion.Rows.Count
With Worksheets("Sheet1").Range("A4")
.Offset(RowCount, 0) = Me.txtName.Value
.Offset(RowCount, 1) = Me.txtSalary.Value
.Offset(RowCount, 2) = benefits
.Offset(RowCount, 3) = total
End With
End Sub
- The first line of code represents a new variable called RowCount. Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647.
- RowCount = Worksheets("Sheet1").Range("A4").CurrentRegion.Rows.Count: This statement counts how many rows of data are included in the region that includes cell A4 and stores that number in the RowCount variable.
- Now using the Offset property which requires two
numbers, the
first representing the number of rows (RowCount) away from cell A4 ,
the second representing the number of columns away from cell A4, we can
access the appropriate cells in the Excel worksheet to automatically
enter the name, salary, benefits and the total package of the employee .
Watch the Excel VBA training video below to understand the details.