excel - How to filter table with RegEx in VBA?

Question

I have a table with column of IP Address, some of them have a mask with CIDR notation (1.1.1.1/16 for example).

I would like to filter that table with a regex expression, I tried it in Regex101 and it is what I need :

"[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}/(($[0-9]|[0-9] )|(1[0-9]))"

How can I apply the filter to my table with my regex, only show lines where regex is true ?

question from:https://stackoverflow.com/questions/65919070/how-to-filter-table-with-regex-in-vba

Answer 1

First check this is in Tools -> references :

Then you should follow this example :

        Dim current_string As String
        Dim my_pattern As String
        my_pattern = "[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}/(($[0-9]|[0-9] )|(1[0-9]))"
        Dim my_regexp As RegExp
        With my_regexp
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = my_pattern 
        End With
        For i = 1 To N
            current_string = Range("A" & i).Text
            If my_regexp.test(current_string)=False Then
                Range("A" & i).EntireRow.Hidden = True ' we only hide rows that doesn't match the regexp.
            End If
        Next i

Let me know if it helps !