How to refer to, search in and edit specific rows within an Excel ListObject Table (former List)

[JacekKotowski]

Let us imagine that in <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">ListObject</code> table rows there are some strings or some patterns I would like to find, and if found I would like to format the entire row. My example may be a dump from a recordset via VBA and ACE OLEDB into an excel <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">ListObject</code> table and I would like to format it:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">col1 | col2 | col3 | col4
-----+-----------+---------------+----------
1. | empty | AAA |
2. | empty | AAA001 | value
3. | empty | AAA002 | value
4. | Total | desc | value
5. | empty | BBB |
6. | empty | BBB001 | value
7. | empty | BBB002 | value
8. | Total | desc | value

</code>For instance I would like to

1. Bold the entire row in table where the word Total shows up.
2. Insert a row above where the ??? (AAA, BBB,CCC), three letters string denoting a group shows up.

I would probably manage to come up with the code operating on a standard non <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">ListObject</code> table and add rows for the entire worksheet but I have no idea how to search, reference and change rows withinin <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">ListObject</code> Table. Thanks in advance for all suggestions.

 

[Domenic]

Try...

Option Explicit

Sub BoldAndInsertEmptyRow()

    Dim oListObj As ListObject
    Dim RowCnt As Long
    Dim r As Long

    Set oListObj = Worksheets("Sheet1").ListObjects("Table1") 'change the sheet and table names accordingly
    
    RowCnt = oListObj.ListRows.Count
    
    For r = RowCnt To 2 Step -1
        With oListObj.ListRows(r).Range
            If UCase(Trim(.Cells(1, 2).Value)) = "TOTAL" Then 'check second column for the text Total
                .Font.Bold = True
                If r < RowCnt Then
                    oListObj.ListRows.Add Position:=r + 1, alwaysinsert:=True
                End If
            End If
        End With
    Next r

End Sub

Hope this helps!

 

[JacekKotowski]

Thank you Domenic. It works very well indeed! I will also use your code to educate myself on formating tables in Excel.