Add row with new ID

Question

I made a code where I can add a new row to a table:

Private Sub CommandButton1_Click()
    SaveOptions.Hide
    Dim myRow As ListRow
    Dim intRows As Integer
    intRows = ActiveWorkbook.Worksheets("formations").ListObjects("formations").ListRows.Count
    Set myRow = ActiveWorkbook.Worksheets("formations").ListObjects("formations").ListRows.Add(intRows)
    
End Sub

I am now trying to add an automatic ID to that row based on the last ID available. So, if the old highest ID was 123, it would add a row with an ID of 124. If it was 124, it would add one with 125, etc.

Both the worksheet I'm working and the table I want to add the new row are called "formations".

Thanks!

Where from the ID to be incremented should be taken? On which column does this ID exist? Should it be the added row number (in table, I mean...)? Your actual code inserts a row **before the last one** I am afraid... — FaneDuru, Jul 28 '22 at 11:26
Whatever column holds the ID values, I guess you cold do MAX(column) and add 1 to get the new ID... — Foxfire And Burns And Burns, Jul 28 '22 at 11:27
@FaneDuru it does, I've temporarily solved that by adding a new empty row. I'm new to coding, so how can I make it add after the last one? — Hugo Almeida, Jul 28 '22 at 15:28
I am still not able to see where you specify in which table column is the ID to be updated... I also asked if the ID in discussion is the number of the inserted row, or it should be calculated according to the previous one. Is it strictly numeric? — FaneDuru, Jul 28 '22 at 16:51
I would suggest it's not a good idea to get the next ID by looking at the previous ones - eg if you at some point need to move records/rows to a different sheet then those id's will not be visible. A better approach is to store the "last-used" id in a specific worksheet cell or a defined workbook Name: then you can easily read it, increment by one and use that as the new Id (also updating your stored value) — Tim Williams, Jul 28 '22 at 22:19

Brody Toth · Answer 1 · 2022-07-28T16:29:29.370

I have found a way to do what you have asked. I had to adjust your original code so that the row added was at the end of the table, and I had to decide where the ID column would be. I have included some instruction on how the column can be changed. Additionally, I made the sub in a way that it will check if IDs are present in the first row of the table. This is done so that if no IDs are in the sheet it will operate as intended.

Private Sub CommandButton1_Click()

'@Variable myRow is the row which will be added to the table.
Dim myRow As ListRow

'@Variable modBook is the workbook containing the table to be modified. 
'Change this by using a specific workbook if the active book is not the one 
'containing the table.
Dim modBook As Workbook
    Set modBook = ActiveWorkbook
    
'@Variable modSht is the sheet containing the table to have a row added to. 
'Change this by changing the sheet name.
    Dim modSht As Worksheet
        Set modSht = modBook.Worksheets("Formations")
    
'@Variable modTable is the table to be modified. Change this by changing the name of the table.
Dim modTable As ListObject
    Set modTable = modSht.ListObjects("formations")
    
'@Variable intRows is the number of rows in the table with one extra so that the row will be placed
'at the end of the table, and so that the number will be placed into the newest row.
Dim intRows As Integer
    intRows = modTable.ListRows.Count + 1
    
'@Variable IDNum is the ID Number to be put into the new column.
Dim IDNum As Integer

'@Variable IDCol is the column that contains the ID Numbers. Change this by changing "A" to another column.
Dim IDCol As Range
    Set IDCol = modSht.Range("A2:A" & intRows)
    
'Checks if the first cell with an ID Number is not 1. If it is not it adds ID numbers to every row in the table
'then adds the new row and gives it the ID Number next in line.
If IDCol.Cells(2, 1).Value < 1 Then
    IDNum = 1
    For Each cell In IDCol
            cell.Value = IDNum
            IDNum = IDNum + 1
    Next
    
    Set myRow = modTable.ListRows.Add(intRows)
    IDCol.Cells(intRows, 1).Value = IDNum
'If the first cell is 1 or greater then the ID Column contains IDs so the last row in the table will become the new
'ID Number.
Else
    IDNum = Cells(intRows, 1).Value + 1
    Set myRow = modTable.ListRows.Add(intRows)
    modSht.Cells(intRows + 1, 1).Value = IDNum
End If

End Sub

If you have any questions about how it works, please let me know. Hope this helps! Edited to include changes so that IDs are printed to the "Formations" sheet.

Hey! Thanks! It however has a little problem - it adds the IDs to the active worksheet, not to the formations one. I'm seeing if I can fix it but if u could help it it would be appreciated! — Hugo Almeida, Jul 28 '22 at 15:39
That should be a simple fix. If you add `modSht` to the `Cells().Value = IDNum` lines it should go to that sheet. Any line that sets the value of a cell to an IDNum variable must be changed. — Brody Toth, Jul 28 '22 at 16:04

score 1 · Answer 2 · answered Jul 29 '22 at 01:28

Excel Table (`ListObject`): Add a New Row With a New Id

Short

Sub AddTableRow()

    ' Reference the table ('tbl').
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Worksheets("Formations").ListObjects("Formations")
    
    ' Reference the id list column ('ilc').
    Dim ilc As ListColumn: Set ilc = tbl.ListColumns("Id")
    
    ' Add and reference a new list row.
    With tbl.ListRows.Add
        ' Write the new id to the new id cell.
        .Range.Cells(ilc.Index).Value = Application.Max(ilc.DataBodyRange) + 1
    End With
    
End Sub

Step By Step

Sub AddTableRowStudy()

    ' Define constants.
    Const wsName As String = "Formations"
    Const tblName As String = "Formations"
    Const ilcName As String = "Id"
    
    ' Reference the objects.
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    Dim tbl As ListObject: Set tbl = ws.ListObjects(tblName)
    Dim ilc As ListColumn: Set ilc = tbl.ListColumns(ilcName)
    
    ' Write the maximum number in the id column's data body range (no headers),
    ' increased by 1 (next id), to a long variable ('NewId').
    Dim NewId As Long: NewId = Application.Max(ilc.DataBodyRange) + 1
    
    ' Add and reference a new list row ('nlr').
    Dim nlr As ListRow: Set nlr = tbl.ListRows.Add
    
    ' Using the 'Index property of the ListColumn object',
    ' reference the new cell ('nCell'), the cell
    ' in the id column's new (last) list row.
    Dim nCell As Range: Set nCell = nlr.Range.Cells(ilc.Index)
    
    ' Write the new id to the new cell.
    nCell.Value = NewId
    
End Sub

Add row with new ID

2 Answers2

Excel Table (ListObject): Add a New Row With a New Id

Excel Table (`ListObject`): Add a New Row With a New Id