One of the most useful tools in Excel is tables. They are a fantastic tool to present our data. It would be pretty cool if we could make Excel change certain values in our table based on the set of values we already have in place.
Luckily, there is a solution for this, and it can be done by using VBA. We will show that in the example below.
Insert Default Value Inside Excel Table
For our example, we will use the list of revenues and expenses throughout the years. Years will be in column A:
Now we want to automatically add the year in column A when we create another row beneath our final row (in this case row number 9). However, we do not want to add years if we add a column anywhere in the existing table (from rows 1 to 9). The reason for this is that we already have years defined, and inserting columns in the middle of the existing table would be considered a mistake.
To do this exercise, we will open up the VBA by clicking ALT + F11, and then find our sheet, which is Sheet1 (we called it Table) in our example and insert the following code in that window:
Private Sub Worksheet_Change(ByVal Target As Range) Dim Table As Range Dim c As Range c = Range("a1").End(xlDown).Value Set Table = Target.Worksheet.ListObjects(1).Range If Target.Row <> (Table.Row + Table.Rows.Count - 1) Then End End If If Target.Worksheet.Cells(Target.Row, 1) = "" Then Target.Worksheet.Cells(Target.Row, 1) = c + 1 End If End Sub
The code looks like this in the VBA module:
The first part of the code, which is:
Private Sub Worksheet_Change(ByVal Target As Range)
Tells us that our code will be triggered by the change in our worksheet. We also declare the variable Target as a range.
Then we declare the variables “Table” and “c” and we set their values:
Dim Table As Range Dim c As Range c = Range("a1").End(xlDown).Value Set Table = Target.Worksheet.ListObjects(1).Range
Variable “c” will have a value of our last populated cell in column A, while our variable “Table” will be equal to the first object that we have in our worksheet. Since we know that we only have one object- our table, our variable will correspond to that table.
The next part of our code is:
If Target.Row <> (Table.Row + Table.Rows.Count - 1) Then End End If If Target.Worksheet.Cells(Target.Row, 1) = "" Then Target.Worksheet.Cells(Target.Row, 1) = c + 1 End If
And it is the main part of it. It first searches if the added row in our sheet is lesser than the total number of our rows (if it was added in the middle of our table, for example) and in that case, it ends our code.
If not, and if our row is added at the bottom of the table, our code first searches for the value in the first column of the row, and if it is empty, it adds one more number to the number of our “c” variable.
To try out od code, we will first input the row in the middle of the table:
As seen, nothing happens. It is worth mentioning that, since our code is located in our sheet, and it reflects changes that are made into the sheet, there is no need to run it separately. It is being run every time we do changes in the sheet.
We will delete the row that we added, and we will input the number for revenues in the 10th row:
As we do that, we will notice that the number 2017 was automatically added to our table.
We will add expenses in row 11, and the same result will follow:
Of course, we could make our code to add any number that we want, and in the column of our choosing, by tweaking it up a little bit.