Author: Oscar Cronquist Article last updated on January 09, 2018

In this small tutorial I am going to show you how to create basic data entry with a small amount of vba code.

Cell B3 and C3 are input cells. When you click button "Add", the data in cell B3 and C3 are copied to the first empty row in the list.

The last step in the code removes the values in cell B3 and C3.

Create macro

  1. Open vb editor, press Alt+F11
  2. Insert a new module

Step 1 - Find last nonempty cell in column B

We can find the last nonempty cell in column B with this code:

Lrow = Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row

We want the first empty cell so we add one to the code.

Lrow = Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row + 1

This line of code saves the value in the variable Lrow. In the example given, the code saves the value 8 in Lrow.

Step 2 - Copy values

Let´s use the value in Lrow and copy values from cell B3:C3 to the first empty row.

Worksheets("Sheet1").Range("B" & Lrow & ":C" & Lrow) = Worksheets("Sheet1").Range("B3:C3").Value


Worksheets("Sheet1").Range("B8:C8") = Worksheets("Sheet1").Range("B3:C3").Value

Step 3 - Remove values

Worksheets("Sheet1").Range("B3:C3").Value = ""

Step 4 - Optimize code

Worksheets("Sheet1") is repeated many times. Let me show you how to optimize the code.

Sub AddText()
Dim Lrow As Single
With Worksheets("Sheet1")
   Lrow = .Range("B" & Rows.Count).End(xlUp).Row + 1
   .Range("B" & Lrow & ":C" & Lrow) = .Range("B3:C3").Value
   .Range("B3:C3").Value = ""
End With
End Sub

Step 5 - Create button

  1. Go to developer tab
  2. Click "Insert" button
  3. Click "Button (Form Control)"
  4. Create a button
  5. Right click on your new button
  6. Assign a macro
  7. Select "AddText" macro

Final note:

There is a data entry form in excel but it is not on the ribbon.

Download excel file *.xlsm

Basic data entry.xlsm