CODEWITHSUNDEEP
vbaexcellistobjectexcel-tables
Jacek Kotowski
Jacek Kotowski

Reputation: 704

How to find (and edit) specific row with an Excel ListObject Table (former List)

Let us imagine that in ListObject 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 ListObject table and I would like to format it:

col1 | col2      | col3          | col4
-----+-----------+---------------+----------
1.   | empty     | AAA           | 
2.   | empty     | AAA001value   | value
3.   | empty     | AAA002value   | value
4.   |  Total    | desc          | value
5.   | empty     | BBB           | 
6.   | empty     | BBB001value   | value
7.   | empty     | BBB002value   | value
8.   |  Total    | desc          | value

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 ???, three letters string denoting a group shows up.

I would probably manage to come up with the code operating on a standard non ListObject table and add rows for the entire worksheet but I have no idea how to search and change rows in ListObject Table.

Upvotes: 0

Views: 1702

Answers (1)

shA.t
shA.t

Reputation: 16958

In VBA code you can find your string patterns by using like compare operator or using Instr() function like below samples:

If (strCell like "*Total*") Then
    ' True for case-sensitive strCell string like "Total", "aTotalb" and not for "total"
End If

If (strCell like "*[A-Z][A-Z][A-Z]00[1-2]*") Then
    ' True for strCell strings with 3 Upper-Case letters And "00" after that and also "1" or "2" after that like "AAA001" or "ZBC002"
    If (InStr(1, strCell, "AAA") > 0) Then
        ' With this InStr() you can remove 'ZBC002' 
    End If  
    ...
End If

If (strCell like "*[*][*][A-Z][A-Z][A-Z][*][*]*"
    ' True for strCell like "a**AAA**b" or "asd**ZCB**"
    If (InStr(1, strCell, "AAA") > 0) Then
        ' With this InStr() you can remove "asd**ZCB**"
    End If  
End If

And for highlighting you can use a code like this:

Sheets(1).Rows(1).Font.Bold = True

Upvotes: 1

Related Questions