VBA - Insert row before the Total row in my table

[JoshLyman]

I have the below table on a sheet called 3 - OTJ LOG which has a Total row at the bottom, and will always be filled (I just don't want to have to add many many unnecessary rows, without knowing how many).

DRAFT - OTJ Log - v0.1.xlsm
	B	C
9	Date	OTJ hrs
10	25/08/2023	3
11	26/08/2023	5
12	27/08/2023	6
13	28/08/2023	6
14	29/08/2023	3
15	30/08/2023	5
16	31/08/2023	7
17	01/09/2023	3
18	Total	38
3 - OTJ LOG

Cell Formulas
Range	Formula
C18	C18	=SUBTOTAL(109,[OTJ hrs])

Is there a way (using VBA) for me to have an a button which the user clicks and it adds a new row into this table before the Total row?

 

[Alex Blakenburg]

All the user really needs to do is hit <Tab> when they are in the last cell of the table.
But if you really need a macro do to do it then try this:

Sub AddRowToTable()

    Dim tbl As ListObject
    
    Set tbl = Range("tblOTJ").ListObject    ' <---- Change to Actual Table name
    
    tbl.ListRows.Add
    
End Sub

 

[JoshLyman]

Thanks @Alex Blakenburg! Works a treat.

 

[Alex Blakenburg]

You're welcome. Glad I could help.