locked
Add a row to a ListObject in Excel with VBA RRS feed

  • Question

  • Is there any way to add a row or column to a ListObject's range without knowing the range?  I have several worksheets that I'm looping through, and each has it's own ListObject with varying ranges.  Therefore I won't know the range and cannot simply use the resize method of ListObjects.  I saw an example on this website that used "ListObjects.ListRows.Add", but wasn't able to get it to work.  New to VBA so any input much appreciated.  Thanks!

    To clarify, if my ListObject's range is A1:C3, I need to add a row to make it A1:C4.  The catch is I won't know the range, so can't use "ListObjects.resize".

    Wednesday, August 29, 2012 5:55 PM

Answers

  • The following code adds a row to a ListObject. Contrary to your comment in initial post "ListObjects.resize" can be used by first extracting the range and then reallocate a range with an extra row.

    Sub ResizeListObject()
        Dim lstObj As ListObject
        Dim rngLstObj As Range
       
        Set lstObj = Sheets("Sheet1").ListObjects("Table1")
        Set rngLstObj = lstObj.Range    'Assign initial range to a range variable
       
        'Resize the range variable with extra row
        With rngLstObj
            Set rngLstObj = .Resize(.Rows.Count + 1, .Columns.Count)
        End With
       
        'Assign the new range to the ListObject
        lstObj.Resize rngLstObj

    End Sub

    Following added with Edit:

    You can reduce the code a little by combining the range Resize with the ListObject Resize.

    Note that there is a space between the first .Resize for the ListObject and .Range

    Sub ResizeListObject_2()

        Dim lstObj As ListObject
       
        Set lstObj = Sheets("Sheet1").ListObjects("Table1")
       
        With lstObj
            .Resize .Range.Resize(.Range.Rows.Count + 1, .Range.Columns.Count)
        End With

    End Sub


    Regards, OssieMac




    • Proposed as answer by Asadulla JavedEditor Thursday, August 30, 2012 5:42 AM
    • Edited by OssieMac Thursday, August 30, 2012 6:01 AM
    • Marked as answer by Looshsmoot Thursday, August 30, 2012 9:24 PM
    Thursday, August 30, 2012 1:54 AM

All replies

  • This is my way to using listobject, but I do not thing that you want to use this way.. take look:

    'Add name first
    'ThisWorkbook.Names.Add Name:="aaa", RefersToR1C1:="=Sheet1!R1C1:R3C2"
    
    Sub Import()
    Dim ok As Boolean
    Dim MyFile$: MyFile = "C:\Temp\test.xlsx"
    Dim MyName$: MyName = "aaa"
    
    ok = tabela(MyFile, MyName, Rows(Rows.Count).End(xlUp).Row)
    Debug.Print ok
    End Sub
    
    Function tabela(ByVal MyFile$, MyName$, Optional MyEndStart&) As Boolean
    Dim MyPath$: MyPath = Left(MyFile, InStrRev(MyFile, "\"))
    If Len(MyEndStart) = 0 Or MyEndStart = 1 Then MyEndStart = 0
    If InStr(1, MyName, " ") Then MyName = "'" & MyName & "$'"
    On Error GoTo blad
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
            Array("ODBC;DSN=Excel Files;DBQ=" & MyFile & ";"), _
            Array("DefaultDir=" & MyPath & ";DriverId=1046;MaxBufferSize=2048;"), _
            Array("PageTimeout=5;")), _
            Destination:=range("$A$" & MyEndStart + 1)).QueryTable
            .CommandText = Array("SELECT * FROM `" & MyFile & "`.`" & MyName & "`")
            .Refresh BackgroundQuery:=False
        End With
    tabela = True
    Exit Function
    blad:
    tabela = False
    End Function

    You can add some sample throw Skydrive or other, and describe little more.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Wednesday, August 29, 2012 6:07 PM
    Answerer
  • It's difficult for me to follow all of this since I'm not a programmer and new to vba.
    Wednesday, August 29, 2012 8:15 PM
  • Is there a way to use the following to add more than one row?

    Set myNewColumn = ActiveWorkbook.Worksheets(1).ListObject(1).ListRows.Add
    Wednesday, August 29, 2012 8:22 PM
  • The following code adds a row to a ListObject. Contrary to your comment in initial post "ListObjects.resize" can be used by first extracting the range and then reallocate a range with an extra row.

    Sub ResizeListObject()
        Dim lstObj As ListObject
        Dim rngLstObj As Range
       
        Set lstObj = Sheets("Sheet1").ListObjects("Table1")
        Set rngLstObj = lstObj.Range    'Assign initial range to a range variable
       
        'Resize the range variable with extra row
        With rngLstObj
            Set rngLstObj = .Resize(.Rows.Count + 1, .Columns.Count)
        End With
       
        'Assign the new range to the ListObject
        lstObj.Resize rngLstObj

    End Sub

    Following added with Edit:

    You can reduce the code a little by combining the range Resize with the ListObject Resize.

    Note that there is a space between the first .Resize for the ListObject and .Range

    Sub ResizeListObject_2()

        Dim lstObj As ListObject
       
        Set lstObj = Sheets("Sheet1").ListObjects("Table1")
       
        With lstObj
            .Resize .Range.Resize(.Range.Rows.Count + 1, .Range.Columns.Count)
        End With

    End Sub


    Regards, OssieMac




    • Proposed as answer by Asadulla JavedEditor Thursday, August 30, 2012 5:42 AM
    • Edited by OssieMac Thursday, August 30, 2012 6:01 AM
    • Marked as answer by Looshsmoot Thursday, August 30, 2012 9:24 PM
    Thursday, August 30, 2012 1:54 AM