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 ).
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?