How To Find Last Row using Tables [Excel VBA]

This tutorial will cover how to find the Last Used Row When Using Tables in Excel VBA.

Tables allow us to organise our data in a certain manner so that it is easier to perform any further manipulations. This blog is not only about finding the last row in an Excel table, but the intention is to also, encourage you to use tables wherever applicable.

Some scenarios where tables just shine are

  • Connecting to external data sources to extract data easily.
  • Updating Pivot reports based on the table
  • Maintaining formulas and formatting even if data is added or deleted.

Note: This example will differ slightly from the video. In the video, we add a new table column in column L to hold our vlookups. We won’t include this column in this blog.

Create a Table

We have our dataset as above. Let’s convert this to a table. Select any cell within the dataset. Go to the Insert ribbon. Click on Table. The current region of your dataset will get selected. In the pop up box, keep the checkbox ticked since our dataset has headers.

Our table will get created. For the last step, go to the Table Design ribbon and give the table a new name: InvoiceTable

Find Last Row

Note: In all examples, the worksheet code name wsInv is being used. You could refer to the worksheet directly instead: ThisWorkbook.Sheets(“Invoice”)

In VBA, we refer to a table as a List Object. We will first declare a variable as a ListObject and assign our Invoice Table to it. This will give us access to all the properties of the table. We can now grab the last row of the table by counting the rows in the table range.

Sub Find_Last_Row()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Assign a variable to hold our table
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim tblInv As ListObject
Set tblInv = wsInv.ListObjects("InvoiceTable")

Dim lrow As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Find last row, if table header is in row 1 of worksheet
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
lrow = tblInv.Range.Rows.Count
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Find last row, if table header is not in row 1 of worksheet
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
lrow = tblInv.Range.Rows(tblInv.Range.Rows.Count).row
End Sub

Example 1: Identify and Loop Over Data Range

We don’t need the last row to select the table. We can just select it directly.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Select full table
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
tblInv.Range.Select
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Select only body of table, excluding headers
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
tblInv.DataBodyRange.Select

Let’s loop over the data and place the word “Invoice” in every corresponding row in column K. First, we will create a new table column in column K.

Sub Find_Last_Row()

Dim tblInv As ListObject
Set tblInv = wsInv.ListObjects("InvoiceTable")

Dim lrow As Long
lrow = tblInv.Range.Rows.Count

tblInv.ListColumns.Add
tblInv.ListColumns(11).Name = "New Data"

Dim i As Long
For i = 2 To lrow
   wsInv.Range("K" & i).Value = "Invoice"
Next i

End Sub

Example 2: Copy Data Range to New Sheet

We will copy data from our table onto sheet: CopyTo (code name: wsCopyTo). To keep this blog consistent with the video, the copy is done via Copy and PasteSpecial.

Sub Find_Last_Row()

Dim tblInv As ListObject
Set tblInv = wsInv.ListObjects("InvoiceTable")

wsCopyTo.Cells.Clear
tblInv.Range.Copy
wsCopyTo.Range("A1").PasteSpecial xlPasteValues
wsCopyTo.Columns.AutoFit

End Sub

Example 3: Paste Data at Bottom of Current Data Range

We will copy data from sheet: CopyFrom (code name: wsCopyFrom)and paste it at bottom of our data range in wsInv sheet.

Sub Find_Last_Row()

Dim tblInv As ListObject
Set tblInv = wsInv.ListObjects("InvoiceTable")

Dim offrowInv As Long
offrowInv = tblInv.Range.Rows(tblInv.Range.Rows.Count).row
offrowInv = offrowInv + 1

Dim lrowCopyFrom As Long
lrowCopyFrom = wsCopyFrom.Range("A" & wsCopyFrom.Rows.Count).End(xlUp).row

wsCopyFrom.Range("A2:J" & lrowCopyFrom).Copy wsInv.Range("A" & offrowInv)

End Sub

Related Posts