Extend tables

Example files with this article:
  • Extend tables
  • Introduction

    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.

    My solution

    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:

    1. Option 1: always go to the first column of the table
    2. Option 2: stay in the same column as where you clicked to generate the Worksheet_SelectionChange event
    3. Option 3: go to the first column that is not a cell with a formula (if not found, option 1 is chosen)
    You will see this in the codes below.

    Of course, an Excel worksheet can hold multiple tables. So the code should determine which table to extend. A loop solution is presented below.

    Custom coding


    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, 3
    End 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 Next
    End 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.

    Enjoy !


    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links