learn microsoft excel

Find Next Blank Row for Automatic Data Entry in an Excel Worksheet from a UserForm using VBA

Excel 2007
Excel 2003
Macros Excel 2007
Macros Excel 2003
Excel 2010
About Us
Free Ebook
Offline Training

Excel Books

The major drawback of the last userform was that the data could be entered only in specific cells and the data would be overwritten. Now using an appropriate code in the userform command button we can find the next empty row for automatic data entry in our Excel worksheet.
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 .
In the next training video we'll  refine the concepts of the userorm further.

Watch the Excel VBA training video below to understand the details.


Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010
About Us Free Ebook Offline Training Excel Books