How to get data from Excel table using ListObject?

Let’s go back to the basics and learn about getting the data from Excel worksheet. But not just filled worksheet cells with text and numbers. In this article I’m going to show You how to get the Excel table data using ListObject in VBA.

So let me start with the data.

Sample data

For the purpose of this article I took the Financial sample data from Microsoft. Really nice thing not to be forced to make up random data. Anyway, the data is already set as a table named financials.

Let’s use this opportunity and present to You ListObject object, which represents a table on the worksheet.

So having declared the workbook with sample data and set the worksheet:

Dim wb As Workbook
Set wb = Workbooks.Open("...\Financial Sample.xlsx")
    
Dim wsTable As Worksheet
Set wsTable = wb.Sheets(1)

You can declare and set the table financials as ListObject.

With wsTable
    Dim ListObj As ListObject
    Set ListObj = .ListObjects("financials")
End With

After that You can do anything You need. So let’s implement real life issue and assume that You need to count all the rows for the specified Country and Manufacturing Price higher than specified value.

First of all, to be able to easily loop throught the table, get the columns indexes for Country and Manufacturing Price.

Dim countryIndex As Integer, manufacturingPriceIndex As Integer
countryIndex = ListObj.ListColumns("Country").Index
manufacturingPriceIndex = ListObj.ListColumns("Manufacturing Price").Index

Secondly, having the indexes, You can start to think about loop. In case of ListObject You can iterate through every ListRow and get the elements from the specified columns using those defined indexes.

Dim elem As Variant, ctr As Long

For Each elem In ListObj.ListRows
    With elem
        If .Range(1, countryIndex) = country And .Range(1 , manufacturingPriceIndex) > manufacturingMinimumPrice Then
            ctr = ctr + 1
        End If
    End With
Next

Every ListRow item of ListObj.ListRows is 1-row table with all the columns of the table. That’s why You need to use 1 as the row value in the .Range.

.Range(1, countryIndex)

So after that You can print out the results using for example this statement.

Debug.Print "Country: " & country & "; Manufacturing Minimum Price: " & manufacturingMinimumPrice & "; Amount: " & ctr

Writing this as a function for the specified ListObject, Country and Manufacturing Price would be really good approach.

Function countRowsForGivenParamsListObject(ListObj As ListObject  , _
    country As String, manufacturingMinimumPrice As Integer) As String

    Dim countryIndex As Integer, manufacturingPriceIndex As Integer
    countryIndex = ListObj.ListColumns("Country").Index
    manufacturingPriceIndex = ListObj.ListColumns("Manufacturing Price").Index
    
    Dim elem As Variant, ctr As Long
    
    For Each elem In ListObj.ListRows
        With elem
            If .Range(1, countryIndex) = country And .Range(1, manufacturingPriceIndex) > manufacturingMinimumPrice Then
                ctr = ctr + 1
            End If
        End With
    Next
    
    countRowsForGivenParamsListObject = "Country: " & country & "; Manufacturing Minimum Price: " & manufacturingMinimumPrice & "; Amount: " & ctr

End Function

The example results

So if You try to get any data, for example for the table from the table financials, Canada and 100 as the value for the Manufacturing Minimum Price or for the Germany & 200 as minimum price:

Sub getDataFromExcelTable()

    Dim wb As Workbook
    Set wb = Workbooks.Open("...\Financial Sample.xlsx")
    
    Dim wsTable As Worksheet
    Set wsTable = wb.Sheets(1)
    
    With wsTable
        Dim ListObj As ListObject
        Set ListObj = .ListObjects("financials")
    End With
    
    Debug.Print countRowsForGivenParamsListObject(ListObj, "Canada", 100)
    Debug.Print countRowsForGivenParamsListObject(ListObj, "Germany", 200)
    
    wb.Close 0

End Sub

You’ll get the results in the Immediate Window:

Country: Canada; Manufacturing Minimum Price: 100; Amount: 60
Country: Germany; Manufacturing Minimum Price: 200; Amount: 40

That was another way of how to get the data from Excel, but this time the data was formatted as table, so You were able to use ListObject. Really nice and simple alternative for the Excel approach of getting data.