Find Next Blank Row in Excel Worksheet for Data Transfer from User Form

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.


5 thoughts on “Find Next Blank Row in Excel Worksheet for Data Transfer from User Form

  1. Pingback: Transfer Data from User Form to Multiple Worksheets in Excel Workbook Using VBA | Excel VBA Training Videos

  2. Jeffrey

    How old are these instructions? I just spent a good part of a day on the previous version of how to do this on Mr. Takyar’s YouTube video. That doesn’t work anymore, as acknowledged here. So why not take it down from YouTube, or at least say what version of Excel it was for?
    Meanwhile, the instructions above:

    RowCount = Worksheets(“Sheet1″).Range(“A4″).CurrentRegion.Rows.Count
    With Worksheets(“Sheet1″).Range(“A4″)

    Either don’t work anymore, or never worked in Office 2007.

    Please label different versions or take down out of date information – I can’t get the day I wasted back, and I still don’t know how to avoid having my forms wipe out the top line of data every time I try to save new entry information.

    Reply
  3. Pingback: Anonymous

  4. jomar logatoc

    how can i do this on user form only adding data entry one by one to textbox or textfields with multiple lines every data entered adding new row i wish you can help me here thanks

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *