Automatically append a new row to the bottom of a table

[natemoss1218]

Hi all! I am in need of bigger brains than mine.

Basically, let's say I have a table of 5 rows. When I add data to the last, 5th row, I want the table to automatically add a blank, 6th row. Essentially, there should always be one blank row at the end of the table so when I then add data to the 6th row, you guessed it, the table adds a new, blank 7th row.

I've seen this done live in Excel, so I know it's possible. Just not sure if it is through VBA or something that can be adjusted in Excel's table settings.

Thank you in advance!

 

[cmowla]

This can be done by converting your "range" into an "Excel Table".

Just select all of your table/range. Press Ctrl T. Then follow the 1 prompt and click OK.

You can format your table the same as was originally by going to the top and choosing this option (Table Design appears when you have selected at least one cell in the table):

(Or choose one of those color schemes that you like.)

Now, when you click on the bottom-right most cell in your table and press tab, it will add a new row to the table. If you type in a cell to the right of the table, it will add a new column. Etc.

 

[natemoss1218]

Thank you! No other way to do this without hitting tab, correct?

 

[cmowla]

You can type on the line below the table too (just like for adding a new column).

 

[Peter_SSs]

No other way to do this without hitting tab, ... ?

If you are happy to use vba, you could try this Worksheet_Change event code. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by adding/deleting/editing something in the body of the table. If there is something in the last row of the table then a new row should get added at the bottom.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

I have assumed that you have made your table a formal Excel table as described above and this table is the only formal table (ListObject) on the worksheet (or if there are multiple it is index 1)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim LO As Object
 
  Set LO = ActiveSheet.ListObjects(1)
  With LO.DataBodyRange
    If Not Intersect(Target, .Cells) Is Nothing Then
      If WorksheetFunction.CountBlank(.Rows(.Rows.Count)) < .Columns.Count Then
        Application.EnableEvents = False
        LO.ListRows.Add
        Application.EnableEvents = True
      End If
    End If
  End With
End Sub

 

[Peter_SSs]

A follow-up. If entries are deleted from the table then with the above code the table may end up with more than one blank row at the end.
If you always want exactly one blank row at the end then you could try this version instead.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim LO As Object
  
  Set LO = ActiveSheet.ListObjects(1)
  With LO.DataBodyRange
    If Not Intersect(Target, .Cells) Is Nothing Then
      Application.EnableEvents = False
      If WorksheetFunction.CountBlank(.Rows(.Rows.Count)) < .Columns.Count Then
        LO.ListRows.Add
      Else
        Do While WorksheetFunction.CountBlank(.Rows(.Rows.Count - 1)) = .Columns.Count
          LO.ListRows(.Rows.Count).Delete
        Loop
      End If
      Application.EnableEvents = True
    End If
  End With
End Sub