Listobjects: How to see if a listobject exhists in a Wrksht

[Asher]

Hola,

I have a workbook with many worksheets. Each worksheet can have up to 6 of 12 different listobjects on it. I have a naming convention for the listobjects so I will always know what the listobject names could possibly be of the 12.

I need code to run ONLY if the Listobject exists and has at least 1 row that is not empty (these Listobjects are connected to Access queries which return 1 empty row when the query returns no items, when I try to run the code on those, i get an object error that does not come up when the table has data in it).

I am thinking something like:
for each listobject name that exists in the possible array of 12 and is (something that says not blank)
run said code
next listobject that exists and has stuff in it

Any suggestions?

-Gracias

Asher

[HansV]

Do the list objects have a header row?

[Asher]

Yes.

ListObjects.HeaderRowRange

[HansV]

Try this code:

Code: Select all

Sub LoopListObjects()
    Dim wsh As Worksheet
    Dim varName As Variant
    Dim lstObj As ListObject
    ' Loop through worksheets
    For Each wsh In Worksheets
        ' Loop through potential list object names
        For Each varName In Array("Table1", "Table2", "Table3")
            On Error Resume Next
            ' Try to refer to listobject varName
            Set lstObj = wsh.ListObjects(varName)
            On Error GoTo 0
            ' Does it exist?
            If Not lstObj Is Nothing Then
                ' Is the first row non-blank
                If Application.CountA(lstObj.ListRows(1).Range) > 0 Then
                    ' Your code here
                    ' ...
                End If
                ' Reset variable for the next round
                Set lstObj = Nothing
            End If
        Next varName
    Next wsh
End Sub

[Asher]

Yes Hans!!!! This works quite well! I had to change it up a bit for some other complications in my existing code, but the idea of it and the syntax got it all to work together. Your code is always so simple and elegant. I tend to complicate things way too quickly. Thanks so much for your help.