Finding the Next Row in a ListObject in VBA

ListObjects (Tables in Excel’s UI) are structured ranges. I use them constantly. I love the built-in named ranges and referring to them in VBA without a lot of hullabaloo. It’s as close to a database as you’re going to get in Excel. Recently I decided to automate a process of adding some payroll records to the end of a table. If I were using just a range, I would find the next available row like

That works most of the time for ListObjects too. It returns the row right below the last row of the ListObject. In most cases, when you add some data to that row, the ListObject expands. In the case where there is no data in the ListObject and there is only a blank row, however, it doesn’t work. The ListObject doesn’t expand, and even if it did, you would have a blank row.

The ListObject object has a InsertRowRange property that returns a Range object. When a ListObject has no data, it has a header row and a blank row[1] ready to accept data.

When you enter something into that row, it doesn’t give you a new insert row, it just sits there.

When I’m trying to write something to the end of a ListObject, I test to see if InsertRowRange is nothing[1]. Here’s a snippet

If InsertRowRange is Nothing, then table isn’t empty and I offset down however many rows there are plus one. The old method of End(xlup) works in this situation too. I don’t find top down better or worse than bottom up, so use whatever you like. If InsertRowRange isn’t Nothing, that means there’s no data in the table. In that case, I can insert starting in InsertRowRange.

Here’s the whole procedure, if you’re looking for context.

[1]: Now you get the disclaimer. There’s a lot you can do with Tables in Excel. You can have a header row or now header row. You can have a totals row or not. And you can have a bunch of other stuff that makes this code not work. I use Tables a lot from a UI perspective and sometimes I have various features on or off. But the way I’m using a ListObject in this example is as a datastore. It’s not meant to be messed with – only for the VBA to read from and write to. In those cases, I make the Table the only thing on the sheet, it always has a header, and it never has a total row. If you want to use Tables differently, you’ll have to modify the code to accommodate the differences.

Posted in VBA

10 thoughts on “Finding the Next Row in a ListObject in VBA

  1. Another workaround to see if there is no data body range, as opposed to checking the InsertRowRange object, is to check if the DataBodyRange of the table is Nothing. Generally I use a simple function to get the current number of rows in the table:

    Function TABLEROWSCOUNT(ByVal Table As ListObject) As Long
    If Not Table.DataBodyRange Is Nothing Then
    TABLEROWSCOUNT = Table.ListRows.Count
    End If
    End Function

    Generally checking if the DataBodyRange is Nothing is simpler and more intuitive than a legacy object IMHO. Plus you don’t have to worry if the HeaderRowRange is Nothing or not, which it will be if the header row is not showing (ShowHeaders = False). Same with the total row.

    From a book on tables regarding the InsertRowRange object… ;)

    “The InsertRowRange property returns the Table‚Äôs current insertion row. It is a Range object type. The property cannot be set. While this range was always the first free row below the Table in Excel 2003 (the row with the asterisk), it was partially deprecated in Excel 2007 and remains so in 2013. In Excel 2007 and later versions, InsertRowRange only returns the first data row and only when the Table does not contain any data. Otherwise, it returns Nothing and is effectively useless.”

    Penny for my thoughts I guess. :)


  2. Dick, this is great stuff. It clarifies some things I’ve worked around but didn’t really understand.

    In fooling with this, I think that DataBodyRange is the inverse, existence-wise, of InsertRowRange, i.e., if one exists the other doesn’t. There’s also no ListRows when there’s no DataBodyRange. There’s always an ListObject.Range though, and it seems to include either an InsertRowRange or DataBodyRange, whichever exists.

    Armed with this new-found knowledge, here’s another way to do what your code does. It adds the new row before the content instead of letting the new row expand the table. And it uses the ListObject’s “Resize” method – I never pass up the chance to use the two Resizes in the same line!

    Sub AnotherWay()
    Dim lo As Excel.ListObject
    Dim rStart As Excel.Range
    Dim arr(1 To 2) As String

    arr(1) = "Dick"
    arr(2) = "Kusleika"
    Set lo = ActiveCell.ListObject
    lo.Resize lo.Range.Resize(lo.Range.Rows.Count + IIf(lo.InsertRowRange Is Nothing, 1, 0), lo.Range.Columns.Count)
    Set rStart = lo.Range.Rows(lo.Range.Rows.Count)
    rStart = arr
    End Sub

  3. OK, now we’re getting somewhere. I just learned there’s an ListRows.Add method. Now I don’t have to check at all.

  4. Listrows.add is indeed the better option, but you should always check the setting of Application.AutoCorrect.AutoExpandListRange

    Sub YetAnotherWay2()

    Dim lo As ListObject
    Dim rStart As Range
    Dim arr(1 To 2, 1 To 2) As String
    Dim bAutoExpand As Boolean

    arr(1, 1) = "Dick": arr(1, 2) = "Kusleika"
    arr(2, 1) = "Doug": arr(2, 2) = "Glancy"

    Set lo = ActiveSheet.ListObjects(1)

    bAutoExpand = Application.AutoCorrect.AutoExpandListRange ' store setting
    Application.AutoCorrect.AutoExpandListRange = True
    lo.ListRows.Add.Range.Resize(UBound(arr)).Value = arr
    Application.AutoCorrect.AutoExpandListRange = bAutoExpand ' restore setting

    End Sub

    btw: you only have to resize the rows

  5. Assuming:
    – the table has headers
    – the first column doesn’t contain any formulae
    – the table has 5 columns

    Sub M_snb()
    With ListObjects(1).Range
    .Rows(1).Offset(.Columns(1).SpecialCells(2).Count) = Split("ab1 ab2 ab3 ab4 ab5")
    End With
    End Sub

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.