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

w

A

B

C

D

F

F

G

H

I

1

N.iiiif

Am on lit

2

Allen

98;

3

Bill

40S

Elisa

773

OK 1

412

Cancel ]

8

Frank George

551 895

10

Keith

11

12

13

14

15

► HIX Sheet 1/

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

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

Sub GetData()

Dim NextRow As Long

Dim Entryl As String, Entry2 As String

NextRow = Range("A65536").End(xlUp).Row + 1 Entryl = InputBox("Enter the name") If Entryl = "" Then Exit Sub Entry2 = InputBox("Enter the amount") If Entry2 = "" Then Exit Sub

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

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.

To keep things simple, this procedure doesn't perform any validation.

Notice the statement that determines the value of the NextRow variable. If you don't understand how this works, try the manual equivalent: Activate cell A65536 (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).

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.

Was this article helpful?

0 0

Post a comment