VBA excel filtering matching string before 1st '_' and matching string after the 2nd &#039-CodePudding

I just started to learn VBA few days ago and trying to sort out a filtering problem and hope to get some idea.

I have a column of ID that the format is like "C_1_1", "C_1_11", "C_12_11".

The criteria was using the string before the first '' symbol and string after second '' symbol to find matching ID like head and tail e.g. "C_20_2" <-- "C_" and "11". In VBA i tried to use Filter function to filter data that match "C" then 2nd filter that match "_11"

However,the problem I am facing is that because the middle part of the ID is also in a format of "_xx" so there will be unwanted data like in the picture below that all the non-highlighted data is not relevant. Is there any suggestion i can filter or extract only the data in yellow colour? Any suggestion would be appreciated. Thank you so much

CodePudding user response：

You can use a wildcard like:

Dim TInput as String
TInput = Range("H2").Value & "*" & Range("H3").Value

Now you only need one filter, the second is not needed.

CodePudding user response：

Filter Data

Excel

If you have Microsoft 365, without too much thinking, in cell G2 you could simply use:

=FILTER(FILTER(A2:A100,LEFT(A2:A100,LEN(H2))=H2),RIGHT(FILTER(A2:A100,LEFT(A2:A100,LEN(H2))=H2),LEN(H3))=H3)

VBA

Sub FilterData()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("Data")
    
    Dim rg As Range
    Set rg = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp))
    
    Dim sStr As String: sStr = CStr(ws.Range("H2").Value)
    Dim eStr As String: eStr = CStr(ws.Range("H3").Value)
        
    Dim Data(): Data = rg.Value
    
    Dim sr As Long, dr As Long, cString As String
    
    For sr = 1 To UBound(Data, 1)
        cString = CStr(Data(sr, 1))
        If cString Like sStr & "*" & eStr Then
            dr = dr   1
            Data(dr, 1) = cString
        End If
    Next sr
    
    With rg.EntireRow.Columns("G")
        .Resize(dr).Value = Data
        .Resize(ws.Rows.Count - .Row - dr   1).Offset(dr).ClearContents
    End With

End Sub