Tips on Getting Input and Adding a Row of Information to a Spreadsheet

The only trick to adding to a list of information in spread sheet is keeping track of the last row. There are many ways to do this, however, one way is to store the value of the last occupied row in some cell. Let’s assume that the last occupied row is 7, also, let’s choose cell Z2 to store our last row value. Therefore Z2 holds the value 7.

The code needs to get the value of the last row, increment that value by 1 for the new row, and save the incremented row value. We’ll need to temporarily hold on to a copy of the value of the last row in a variable, let’s call it LastRow. This process is done with 3 lines of VBA code:

LastRow = Range("Z2").Value 'get the value of the last row

LastRow = LastRow + 1 'increment the value of the last row by 1

Range("Z2").Value = LastRow 'save the new value for the last row

We can give a cell in the last row “focus” by selecting it. When you select a cell you make it the current active cell and a border is placed around it. This is done like this:

Range("A" + Format(LastRow)).Select 'give the cell in column A focus

Now, to enter information into the cells of the last row we simply choose the columns where it will go. For example, if we want to store some constant text like VISA in the cell in the first column we could use:

Range("A" + Format(LastRow)).Value = "VISA" 'store the text VISA in column A

We can use variables and an InputBox to get and save information gathered from a user. For example, you may want to get the date and store it in a cell in column B. This can be done like this (we’ll also give this cell the focus):

Range("B" + Format(LastRow)).Select 'give the cell in column B focus

MyDate = InputBox("Enter Date", "Date Form", Date) 'get a date

Range("B" + Format(LastRow)).Value = MyDate 'store some date in column B