How to find the last Table row on a sheet

There was a really good blog post by Jon Acampora over at ExcelCampus with a VBA tutorial on finding the last row, column, or cell on a sheet, and three different methods to do that with (End, Find, and SpecialCells). All good methods, and it was written very well.

This got me to thinking, there really isn’t anything for finding the last row or column with Tables on a worksheet. The two routines below will find the last row or column occupied by all Tables on a specified worksheet. If no worksheet is specified it will use the ActiveSheet, and will exit if there is not one of those (for example if the code is housed in your PERSONAL.XLSB or in an add-in, there is a possibility of no ActiveSheet or ActiveWorkbook being present).

 

 

EDIT: From some comments below I’ve made some adjustments. These deserve their own functions rather than incorporating them in the above code. TonyL asked if this could be done for a specified Table, rather than just the entire worksheet which includes all Tables. The below variation takes an additional parameter of the Table if you want to specify it.

 

18 thoughts on “How to find the last Table row on a sheet

  1. Great stuff Zack! This helps resolve the issue I was having with finding the last row on a sheet that contains a blank table. Ctrl+End or the VBA End method doesn’t do the trick. Here’s a screenshot of what I’m referring to.

    Thanks for another great resource!

  2. This is excellent. Thank you. I have a worksheet which contains 30 Tables. Is there a way to isolate each to find the last row? Thank you again. I couldn’t find this anywhere.

      1. Thank you for the reply. I meant the last row of a Table as opposed to just a range on a worksheet. I have a worksheet that hold several independent tables. Each table support a pivot table and chart. Thank you.

        1. Well, the code in the post looks at all tables on a specified worksheet. The idea is to return the last row of the worksheet, not necessarily for a specific table. However, you can incorporate this if you want. The below code is a slight adjustment from the code in the blog post above, and you just submit the table object you want to check if you only want to look at a single, specific table. Test routine is at the top.

          Sub TestLastRow()
          Debug.Print LastTableRow(, ActiveSheet.ListObjects(1))
          Debug.Print LastTableRow(, ActiveSheet.ListObjects(2))
          Debug.Print LastTableColumn(, ActiveSheet.ListObjects(1))
          Debug.Print LastTableColumn(, ActiveSheet.ListObjects(2))
          End Sub

          Function LastTableRow( _
          Optional ByVal TableSheet As Worksheet, _
          Optional ByVal SingleTable As ListObject _
          ) As Long

          ‘ Finds the last row of all Tables on a specified worksheet. If omitted the active sheet is used.

          ‘ Parameters: TableSheet. ListObject. Optional. The worksheet to look at.

          Dim Table As ListObject
          Dim LastRow As Long

          ‘ Get passed sheet object
          If TableSheet Is Nothing And SingleTable Is Nothing Then
          If ActiveSheet Is Nothing Then Exit Function
          Set TableSheet = ActiveSheet
          ElseIf TableSheet Is Nothing And Not SingleTable Is Nothing Then
          Set TableSheet = SingleTable.Parent
          End If

          If Not SingleTable Is Nothing Then

          ‘ Use a single specified table
          If SingleTable.DataBodyRange Is Nothing Then
          LastRow = WorksheetFunction.Max(SingleTable.InsertRowRange.Row + 1, LastRow)
          Else
          LastRow = WorksheetFunction.Max(SingleTable.ListRows(SingleTable.ListRows.Count).Range.Row, LastRow)
          End If
          If SingleTable.ShowTotals Then LastRow = LastRow + 1

          Else

          ‘ Iterate all Tables on specified sheet
          For Each Table In TableSheet.ListObjects
          If Table.DataBodyRange Is Nothing Then
          LastRow = WorksheetFunction.Max(Table.InsertRowRange.Row + 1, LastRow)
          Else
          LastRow = WorksheetFunction.Max(Table.ListRows(Table.ListRows.Count).Range.Row, LastRow)
          End If
          If Table.ShowTotals Then LastRow = LastRow + 1
          Next Table

          End If

          ‘ Assign value
          LastTableRow = LastRow

          End Function

          Function LastTableColumn( _
          Optional ByVal TableSheet As Worksheet, _
          Optional ByVal SingleTable As ListObject _
          ) As Long

          ‘ Finds the last column of all Tables on a specified worksheet. If omitted the active sheet is used.

          ‘ Parameters: TableSheet. ListObject. Optional. The worksheet to look at.

          Dim Table As ListObject
          Dim LastColumn As Long

          ‘ Get passed sheet object
          If TableSheet Is Nothing And SingleTable Is Nothing Then
          If ActiveSheet Is Nothing Then Exit Function
          Set TableSheet = ActiveSheet
          ElseIf TableSheet Is Nothing And Not SingleTable Is Nothing Then
          Set TableSheet = SingleTable.Parent
          End If

          If Not SingleTable Is Nothing Then

          ‘ Use a single specified table
          LastColumn = WorksheetFunction.Max(SingleTable.ListColumns(SingleTable.ListColumns.Count).Range.Column, LastColumn)

          Else

          ‘ Iterate all Tables on specified sheet
          For Each Table In TableSheet.ListObjects
          LastColumn = WorksheetFunction.Max(Table.ListColumns(Table.ListColumns.Count).Range.Column, LastColumn)
          Next Table

          End If

          ‘ Assign value
          LastTableColumn = LastColumn

          End Function

  3. how about the match function trick which finds the end of any list? Here, match(9999999999, tablename[anyfieldthat’sneverblank],1). All you need is subtract the location of the table title line — which one can find the lookup way, but which I look forward to learning how to find via VBA…

    1. True, Stephan, you can use a function to return the last value in a range. The function you described only matches numbers and ignores text. There is a way to do this for text as well…

      =MATCH(REPT(“Z”,255),Range)

      There are several ways to do this with formulas. The hard part with formulas is looking in a range with Tables and not knowing the table name, yet still searching the Table itself for blank data. As far as I know, there is no formula we can use to find the last Table row, but data only, which is the crux of the problem. Blank Table rows are ignored with formulas.

  4. Hi all, how can I use the Function LastTableRow within a sub that inserts a new row before the last row in each table in the same sheet. I have approx. 10 tables and need to insert a row before the last in each table, then copy and paste values from the last row to the penultimate row in order to preserve the formulas in the last row.

    Please help!

    So far I have managed to insert a row at the end of each table with the following code:

    Sub Insert_Row()
    Dim Tbl As ListObject
    With Worksheets("STATIC TRENDED DATA")
    For Each Tbl In ActiveSheet.ListObjects
    Tbl.ListRows.Add (LastTableRow)
    Next Tbl
    End With
    End Sub

    1. Adam,

      I’m not sure you’re looking for the same function. What you want is a little difficult, but only because the way Table rows are inserted is different than a normal Range. I’ve written a couple routines below illustrating this.

      The ‘TestAddRows’ routine is the one to call. It runs an insert to the last row of all Tables on the active worksheet.

      The ‘AddRowsToAllTables’ routine runs the insert to all Tables in the specified sheet. If no sheet is specified it uses the active sheet

      The ‘InsertRows’ routine is where the magic happens. This does all the necessary checks and settings for inserting a row in a Table.

      Sub TestAddRows()
      AddRowsToAllTables
      End Sub

      Sub AddRowsToAllTables( _
      Optional Sheet As Worksheet, _
      Optional ByVal NumberOfRows As Long = 1 _
      )

      ‘ Add a specified number of rows to each Table in the specified worksheet

      Dim Table As ListObject
      Dim NewRow As Range

      If Sheet Is Nothing Then
      If ActiveSheet Is Nothing Then Exit Sub
      Set Sheet = ActiveSheet
      End If

      For Each Table In Sheet.ListObjects
      InsertRows Table, 0, NumberOfRows, True
      Next Table

      End Sub

      Function InsertRows( _
      ByVal Table As ListObject, _
      Optional ByVal Position As Long, _
      Optional ByVal RowCount As Long = 1, _
      Optional ByVal MessageOnError As Boolean = False _
      ) As Long

      ‘ Insert a specified number of blank rows into the Table.

      ‘ Syntax: InsertRows

      ‘ Parameters: Position. Long. Optional. Specified row number to start row insertion. If value is
      ‘ a zero, negative, or not passed, the routine will exit. If omitted rows will be
      ‘ inserted at end of the Table.
      ‘ RowCount. Long. Optional. Specified number of rows to insert into the Table. If
      ‘ a zero or less value is passed the routine will exit. If omitted 1 is assumed.
      ‘ MessageOnError. Boolean. Optional. Specify whether to give user a message
      ‘ on error.

      Dim InsertRange As Range
      Dim HeadersVisible As Boolean

      If Table Is Nothing Then Exit Function
      If RowCount < 1 Then Exit Function If Position < 0 Then Exit Function HeadersVisible = Table.ShowHeaders ' Show headers On Error Resume Next If HeadersVisible = False Then Table.ShowHeaders = True On Error GoTo 0 If Table.ShowHeaders = False And HeadersVisible = False Then If MessageOnError Then MsgBox "Something went wrong. The Table sheet might be protected or the Table can't be shifted.", vbExclamation, "Whoops!" Exit Function End If ' Take care of a simple, single row insert If (RowCount = 1 And Position = 0) Or Table.DataBodyRange Is Nothing Then Table.ListRows.Add If RowCount = 1 And Position = 0 Then Exit Function End If ' Normalize insert row position Position = WorksheetFunction.Max(1, WorksheetFunction.Min(Table.ListRows.Count + 1, Position)) ' Find range to insert rows On Error Resume Next If Table.DataBodyRange Is Nothing Then Set InsertRange = Table.InsertRowRange.Resize(RowCount) Else Set InsertRange = Table.DataBodyRange.Resize(RowCount).Offset(Position - 1) End If On Error GoTo 0 If InsertRange Is Nothing Then If MessageOnError Then MsgBox "Something went wrong. Position could not be found.", vbExclamation, "Whoops!" Exit Function End If ' Perform insert On Error Resume Next If Intersect(InsertRange.EntireRow, Table.Range) Is Nothing Then Table.Resize Table.Parent.Range(Table.Range.Address).Resize(Table.Range.Rows.Count + RowCount, Table.ListColumns.Count) Else Intersect(InsertRange.EntireRow, Table.Range).Insert Shift:=xlDown End If On Error GoTo 0 If InsertRange Is Nothing Then If MessageOnError Then MsgBox "Something went wrong. Rows could not be inserted.", vbExclamation, "Whoops!" Exit Function End If ' Set header visibility to what it was If Not HeadersVisible Then Table.ShowHeaders = HeadersVisible InsertRows = 1 End Function

      1. Zack,
        Many thanks for this. Could you please send excel file with the code by any chance? I’m having difficulty with the Function InsertRows code.
        Thanks

        1. I made a blog post out of this, Adam. The InsertRows function is only designed to work on one Table at a time. That’s why we needed another wrapper routine to loop through all Tables in the workbook, AddRowsToAllTables. Then I showed how to use that wrapper call in two ways, with both TestAddRows and TestAddRows2.

          Does that help any Adam?

          1. Hi Zack,

            Thanks so much for your help on this.

            However, this does appear to achive the same result as the code I intially posted and simply adds a row at the end of each table. Maybe I can adapt the routine and edit the code to do the following for each table (as the new row is being added already):
            1. Copy and paste formulas from penultimate row to last row once the new row has been added at the end of the table
            2. Then in the penultimate row, copy its contents and paste values over its formuals so it is static and will no longer change.
            Any ideas how I would do this? I’m struggling to find anything online because these are tables rather than data ranges.
            Thanks again
            Would like to send you my Excel file if possible – is there a way to attach files?

          2. I see. Well, if you have the default settings, and your columns are set as calculated columns (they are by default), then adding a new row will automatically copy the formulas down to the new rows added. I’m not sure why you would then want to make those static. Perhaps you can explain what it is you are doing and the end result. If this will lengthy, perhaps we could take it offline. Feel free to email me (zackATexceltablesDOTCOM).

  5. Guys,
    I’m confused. I want to find the last row in a table and then add 1 to it, to input data into this same table. I don’t want to insert rows, I just want to put date from a user form into the next empty row. What is happening, is by using Jon’s method, it puts my data at the bottom of the table instead of the next empty row within the table. Can someone tell me just how to find the next empty row inside just one table?

    1. Ross,

      Adding data to a new row in a Table isn’t always straight forward. This is because sometimes the DataBodyRange can be Nothing. But one thing is for certain: if the DataBodyRange is Nothing, then the InsertRowRange is not Nothing, and vice versa. Here is a routine I wrote to InsertRows on a given Table.

      Function InsertRows( _
      ByVal Table As ListObject, _
      Optional ByVal Position As Long = 0, _
      Optional ByVal RowCount As Long = 1, _
      Optional ByVal MessageOnError As Boolean = False _
      ) As Long
      '
      ' Insert a specified number of blank rows into the Table.
      '
      Dim InsertRange As Range
      Dim HeadersVisible As Boolean

      If RowCount > 1 Then Exit Function
      If Position < 0 Then Exit Function
      HeadersVisible = Table.ShowHeaders

      ' Show headers
      On Error Resume Next
      If Table.ShowHeaders = False Then Table.ShowHeaders = True
      On Error GoTo 0
      If Table.ShowHeaders = False And HeadersVisible = False Then
      If MessageOnError Then MsgBox "Something went wrong. The Table sheet might be protected or the Table can't be shifted.", vbExclamation, "Whoops!"
      Exit Function
      End If

      ' Take care of a simple, single row insert
      If (RowCount = 1 And Position = 0) Or Table.DataBodyRange Is Nothing Then
      Table.ListRows.Add
      If RowCount = 1 And Position = 0 Then Exit Function
      End If

      ' Normalize insert row position
      If Position = 0 Then
      Position = Table.ListRows.Count + 1
      Else
      Position = WorksheetFunction.Max(1, WorksheetFunction.Min(Table.ListRows.Count + 1, Position))
      End If

      ' Find range to insert rows
      On Error Resume Next
      If Table.DataBodyRange Is Nothing Then
      Set InsertRange = Table.InsertRowRange.Resize(RowCount)
      Else
      Set InsertRange = Table.DataBodyRange.Resize(RowCount).Offset(Position - 1)
      End If
      On Error GoTo 0
      If InsertRange Is Nothing Then
      If MessageOnError Then MsgBox "Something went wrong. Position could not be found.", vbExclamation, "Whoops!"
      Exit Function
      End If

      ' Perform insert
      On Error Resume Next
      If Intersect(InsertRange.EntireRow, Table.Range) Is Nothing Then
      Table.Resize Table.Parent.Range(Table.Range.Address).Resize(Table.Range.Rows.Count + RowCount, Table.ListColumns.Count)
      Else
      InsertRange.Insert
      End If
      On Error GoTo 0
      If InsertRange Is Nothing Then
      If MessageOnError Then MsgBox "Something went wrong. Rows could not be inserted.", vbExclamation, "Whoops!"
      Exit Function
      End If

      ' Set header visibility to what it was
      If Not HeadersVisible Then Table.ShowHeaders = HeadersVisible

      InsertRows = 1

      End Function

      You can call it like this …

      Sub testInsertRows()
      InsertRows ActiveCell.ListObject, , 1
      End Sub

Leave a Reply to Jon Acampora Cancel reply

Your email address will not be published. Required fields are marked *