wimgielis.com

Extend tables

• Oct. 20, 2016

	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

becomes:

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 !

• « Previous Excel article
• Next Excel article »