Excel: Add new rows to a table with a subtotal row, when data added to a specific column on the last row

How do I get an Excel table (with a subtotal row) to automatically add a new row when I enter data in column C of the last row?

At the bottom-right of the table I have 3 cells with basic SUM formulae, adding up the totals of all the rows above. It acts as a Subtotal row for the table. The table is "formatted as a table", except for these Subtotal and Total rows.

My Table as described above. When Data is added to C22, the table needs to expand to include row 23

When I make another table in the same sheet, rows are automatically added, so I know the autocorrect settings in my Excel are correct. I know I can add rows by pressing Tab when a cell in last row is selected, but I really need it to happen when data is added to a cell in column C in the last row of the table. In other words, I need there to always be a "blank" last row so data can always be added to the last row and be included in the table. (I say "blank" in inverted commas because there are formulas in the blank rows)

The best I've been able to do is to get the following macro going when I press Ctrl+N: (Here's the code of that macro from "module 1")

     Sub AddRow()
'
' AddRow Macro
'
' Keyboard Shortcut: Ctrl+n
'
    ActiveCell.Offset(0, 3).Range("A1").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    ActiveCell.Offset(0, 2).Range("A1").Select
End Sub

That is exactly what I need the macro to do, but I need it to happen when text is added to the last row of the table in column C. The trigger cell will obviously go down as the table gets bigger, and I haven't found a way to do this.

Please note that I know almost nothing about coding; I'm leaing Excel as I go along. I made this macro by clicking "record macro" and pressing the upward arrow and then Tab 5 times to get to the same column on the next line

Any help would be greatly appreciated!

---

In case anyone wants to know, this is for a Point of Sales (POS) system I have set up for a fledgeling business. My setup is:

1. Excel 2010
2. VLOOKUP with a reference sheet containing all product codes, descriptions and prices
   3. A USB barcode scanner I picked up for $50. It is detected as a keyboard, and sends text followed by an "Enter" key to get to the next line.

I need this table to expand downward to accommodate any number of items a customer may want to buy. I could keep pressing Tab, but the cashiers I employ are not technology-inclined and I want to make it as easy and fast as possible for them, which will be particularly valuable when the lines get busy.

Thank you in advance for any help you can provide

Recent Questions...