Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
Dim NextRow As Long
Dim Entry1 As String, Entry2 As String
‘Determine next empty row
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
‘ 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
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.
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 statement works, try the manual equivalent: Activate the last cell in column A (cell
A1048576 in an Excel 2010 workbook), 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’s 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 that 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. Writing additional code to account
for this possibility would be fairly easy to do.
Pausing a macro to get a user-selected range
In some situations, you may need an interactive macro. For example, you can create a macro that
pauses while the user specifies a range of cells. The procedure in this section describes how to do
this with Excel’s InputBox method.
Don’t confuse Excel’s InputBox method with VBA’s InputBox function. Although
these two items have the same name, they’re not the same.