- Oct. 20, 2016
|Example files with this article:|
Table functionality in Excel is great and takes care of a lot of annoyances you would have without them. For example, automatically extending formulas to all rows in the table, making for formulas that are far more readable, etc. But what I do not really like is how we add a new record to the table.
- Either you have to go to the last column in the table and press Tab
- Either you use the mouse, right-click a cell and "Insert rows below"
- Either you have to select a cell in the line just below the table, in a column that is not calculated by a formula, and start typing. Then the table will extend.
Note: If you enable the Totals row of the table, the third option is not possible anymore.
So, can we do this in a smarter way ? Yes, by using some custom VBA. I set up mine and I generously copy it here for my regular blog readers. Remarks or improvements are always greatfully acknowledged.
I use the Worksheet_SelectionChange() event. It is fired whenever a new selection is done by the user, either by the mouse, keyboard, VBA-code. In that event, I look which cell or cells were selected by the user. If the selection happens to be in a row 1 below the table (not leaving a blank row) then this will launch code to extend that table with 1 row.
Example: just selecting cell A16 even without typing anything, adds a new row to the table. But I equally allow to select cells B16, C16, D16 or E16, or combination of these. The end result is adding a new row.
Could we also insert 5 new lines automatically ? Sure! Select cells A16:A20 and the code will add 5 lines :-)
One of the drawbacks of the method with pressing Tab is that you always go to a new line but in the first column of the table. That could be okay but if the table contains 3 columns on the left that are calculated, that is not very useful I guess. You would like to jump to the 4th column instead for data input. My code also allows 3 ways to deal with that sitation:
- Option 1: always go to the first column of the table
- Option 2: stay in the same column as where you clicked to generate the Worksheet_SelectionChange event
- Option 3: go to the first column that is not a cell with a formula (if not found, option 1 is chosen)
Of course, an Excel worksheet can hold multiple tables. So the code should determine which table to extend. A loop solution is presented below.
Columns A and E in the file are calculated with the RAND function so you will see the results in these columns change when you recalculate or open the workbook :-)
Here is the code for the Worksheet_SelectionChange event:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)AddRowsToTable Target, 3End Sub
After that, we have the main code in a regular Module:
Sub AddRowsToTable(r As Range, Optional iChoiceOfColumn As Integer = 3)Dim lo As ListObject Dim sTableName As String Dim oNewRow As ListRow Dim rng As Range Dim i As Long ''the selection cannot be inside a table ''if not, just clicking a table cell would always add rows On Error Resume Next sTableName = r.Cells(1).ListObject.Name On Error GoTo 0 If Len(sTableName) > 0 Then Exit Sub End If ''look for a table that matches the selection For Each lo In r.Parent.ListObjects On Error Resume Next sTableName = r.Cells(1).Offset(-1).ListObject.Name On Error GoTo 0 If Len(sTableName) > 0 Then ''we found a table, now insert row(s) For i = 1 To r.Rows.Count Set oNewRow = lo.ListRows.Add(AlwaysInsert:=True) If i = 1 Then Set rng = oNewRow.Range Next ''select a cell in the first of the new rows 'we can: '1. go to the first column of the table '2. stay in the same column as where the user clicked '3. go to the first column that is blank (if not found, option 1 is chosen instead) Application.EnableEvents = False Select Case iChoiceOfColumn Case 1: 'always go to the first column of the table Application.Intersect(rng, lo.DataBodyRange.Columns(1)).Select Case 2: 'stay in the same column as where the user clicked Application.Intersect(rng, r.Parent.Columns(r.Column)).Select Case 3: 'go to the first column that is not a cell with a formula (if not found, option 1 is chosen) On Error Resume Next rng.SpecialCells(xlCellTypeBlanks).Cells(1).Select If Err.Number Then Err.Clear Application.Intersect(rng, lo.DataBodyRange.Columns(1)).Select End If On Error GoTo 0 End Select Application.EnableEvents = True Exit For End If NextEnd Sub
I always use this in my files as I find it quite useful, and in general, I tend to use tables wherever I can.