Entering a value in the next empty cell

A common requirement is to enter a value into the next empty cell in a column or row. The following example prompts the user for a name and a value and then enters the data into the next empty row (see Figure 11-5 ).

Figure 11-5: A macro for inserting data into the next empty row in a worksheet.


Dim NextRow As Long

Dim Entry1 As String, Entry2 As String 'Determine next empty row

Prompt for the data

Entry1 = InputBox("Enter the name") If Entry1 = "" Then Exit Sub Entry2 = InputBox("Enter the amount") If Entry2 = "" Then Exit Sub

' Write the data

Cells(NextRow, 1) = Entry1 Cells(NextRow, 2) = Entry2 Loop End Sub

To keep things simple, this procedure doesn't perform any validation. Notice that the loop continues indefinitely. I use Exit Sub statements to get out of the loop when the user clicks Cancel in the input box. CD-ROM

The GetData procedure is available on the companion CD-ROM. The filename is next empty cell.xlsm.

Notice the statement that determines the value of the NextRow variable. If you don't understand how this works, try the manual equivalent: Activate the last cell in column A, press End, and then press the up-arrow key. At this point, the last nonblank cell in column A will be selected. The Row property returns this row number, and it is incremented by 1 in order to get the row of the cell below it (the next empty row). Rather than hard-code the last cell in column A, I used Rows.Count so this procedure will work with previous versions of Excel (which have fewer rows).

Note that this technique of selecting the next empty cell has a slight glitch. If the column is completely empty, it will calculate row 2 as the next empty row. It would be fairly easy to write additional code to account for this possibility.

Was this article helpful?

0 0


  • Vito Cattaneo
    How to data entry in next emty cell vba?
    2 years ago

Post a comment