Looping through tables in vba, ignore empty tables?

[Lil2606]

Hi all,

So I have this following code (piece):

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">
Dim i As Long
Dim LOC As Long

If Not Sheet4.ListObjects("BLANKTable").DataBodyRange Is Nothing Then

LOC = Sheet4.ListObjects.Count

ElseIf Sheet4.ListObjects("BLANKTable").DataBodyRange Is Nothing Then

LOC = Sheet4.ListObjects.Count - 1

End If

For i = 1 To LOC

Sheet4.ListObjects(i).DataBodyRange.ClearFormats
Sheet4.ListObjects(i).DataBodyRange.ClearFormats
Sheet4.ListObjects(i).DataBodyRange.ClearFormats
Sheet4.ListObjects(i).DataBodyRange.ClearFormats

Sheet4.ListObjects(i).ListColumns("One of the columns").DataBodyRange.Interior.Color = RGB(207, 207, 237)
Sheet4.ListObjects(i).ListColumns("Another one").DataBodyRange.Interior.Color = RGB(245, 212, 231)
Sheet4.ListObjects(i).ListColumns("A column").DataBodyRange.Interior.Color = RGB(220, 209, 233)
Sheet4.ListObjects(i).ListColumns("A different column").DataBodyRange.Interior.Color = RGB(220, 209, 233)

Next i</code>

Which was working perfectly until I had 5 tables and the last one was the "BLANKTable" but I had to add a new table, to which the interior colour change should apply.. but if the BLANKTable is empty then the code throws an error. (and I realized other tables may end up empty as well before this step, so this code piece is really not right)

I can see why.. but I don't know how to loop through all the tables except for the ones that's databody range is empty.. which is what I would need here I think. Could someone help with this?

Question posted on Excelforum as well. Link: https://www.excelforum.com/excel-pr...-in-vba-igrnore-empty-tables.html#post5120216

 

[Lil2606]

Re: Looping through tables in vba, igrnore empty tables?

yeah.. I had the solution in the question....

For i = 1 To Sheet4.ListObjects.Count

If Not Sheet4.ListObjects(i).DataBodyRange Is Nothing Then


Sheet4.ListObjects(i).DataBodyRange.ClearFormats
Sheet4.ListObjects(i).DataBodyRange.ClearFormats
Sheet4.ListObjects(i).DataBodyRange.ClearFormats
Sheet4.ListObjects(i).DataBodyRange.ClearFormats


<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sheet4.ListObjects(i).ListColumns("One of the columns").DataBodyRange.Interior.Color = RGB(207, 207, 237)
Sheet4.ListObjects(i).ListColumns("Another one").DataBodyRange.Interior.Color = RGB(245, 212, 231)
Sheet4.ListObjects(i).ListColumns("A column").DataBodyRange.Interior.Color = RGB(220, 209, 233)
Sheet4.ListObjects(i).ListColumns("A different column").DataBodyRange.Interior.Color = RGB(220, 209, 233)</code>


End If


Next i

just ignore me please!