How do I add a new row to an Excel table using VBA ?

well your table has surely a name if you created it via vba then you know the name or else by doing this provided there is 1 table in the worksheet
activesheet.ListObjects(1).ListRows.Count
you get the number of rows in the table

Now you can call the table if you know the name
activesheet.ListObjects("Table1").ListRows.Count

Now to add a row
activesheet.ListObjects("Table1").ListRows.Add
will add a row at the end of the table

if you want to add a row at the 5th row
activesheet.ListObjects("Table1").ListRows.Add 5

same for the columns
activesheet.ListObjects("Table1").ListColumns.Add

will add a column at the right most of the edget of the table

activesheet.ListObjects("Table1").ListColumns.Add 2 will insert a column in Col B

Hope hv answered your quesitons. Pls feel free to revert if not clear or if you have a practical example you need help with.

gowflow

Hi,
I'm getting a Run Time error 438 'Object doesn't support this method or property' on your first line of code ,,,,,activesheet.ListObjects(1).ListRows.Count
Any reason why ?
Thanks
Toco
 

can you post your code ?
gowflow

PLEASE FIND CODE BELOW
I've only introduced the first line of your code, marked with asterisks.....*****

Private Sub Add_Sales()

' Declare the variables of the 3 worksheets
    Dim wsSales As Worksheet
    Dim wsSalesDetails As Worksheet
    Dim wsTemplate As Worksheet

' Declare variables for the last data row of Sales and Sales Details sheets
    Dim lastRowSales As Long
    Dim lastRowSalesDetails As Long

' Declare variables for template cells
    Dim InvoiceNo        As Long
    Dim InvoiceDate      As Date

' Set each sheet
    Set wsSales = ThisWorkbook.Sheets("Sales")
    Set wsSalesDetails = ThisWorkbook.Sheets("Sales Details")
    Set wsTemplate = ThisWorkbook.Sheets("Template")
   
' Set the last row variables
    lastRowSales = wsSales.Range("A65536").End(xlUp).Row
    lastRowSalesDetails = wsSales.Range("A65536").End(xlUp).Row
    MsgBox (lastRowSales)
   
*****  ERROR OCCURS HERE
    ActiveSheet.ListObjects("Sales").ListRows
*****

' Add new Sales row to end of range
    wsSales.Rows(lastRowSales).Copy
    wsSales.Range("a" & lastRowSales + 1).PasteSpecial xlPasteFormats
    wsSales.Range("a" & lastRowSales + 1).PasteSpecial xlPasteFormulas
    wsSales.Range("a" & lastRowSales + 1).PasteSpecial xlPasteValidation
    wsSales.Rows(lastRowSales + 1).SpecialCells(xlCellTypeConstants, 23).ClearContents
    On Error Resume Next
       
    InvoiceNo = Worksheets("Tables").Cells(2, 6)
    wsSales.Range("a" & lastRowSales + 1).Value = InvoiceNo
   
' Increment the Next Invoice number
    Worksheets("Tables").Cells(2, 6) = InvoiceNo + 1
   
    With Application
       .ScreenUpdating = True
       .CutCopyMode = False
    End With
   
    wsSales.Range("B" & lastRowSales + 1).Select
    MsgBox ("Please enter the Sales details")
   
' Populate the default sales details

    Worksheets("Template").Select
    wsTemplate.Range("G9").Value = InvoiceNo
    InvoiceDate = Date
    wsTemplate.Range("G10").Value = InvoiceDate

End Sub

Hi goflow,

I identified the error in my code as pointed out by yourself. Your code now works fine.

Many thanks for your help
Phil

your welcome tks for the grade.
gowflow