Apply filter based on a character in a string column in VBA

user_15096371 0
Viewed 271

so I am trying to copy unique values from a column after applying several filters to it. For one of the filter I cant filter by exact string because the column is like this:

Actual column

I want the values which have "U" in 8th position from right and want to ignore the ones which have "O" in the same place.

So for example, in the image: 'S20-0260-TA-002_W06_05C_AMBRH_U-404728' should be present but 'S20-0260-TA-001_W06_30C_75RH_O-404713' shouldn't be.

I know how to check a character from right, from the RIGHT function but I am struggling to apply it in a filter with if condition. This is my relevant code so far with other filters:

Dim uniquesArray As Variant
    Dim uniqueCond As Variant
    
    Dim lastCondRow As Long
    Dim lastRow As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("New Data")
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    Set DR = ThisWorkbook.Sheets("Test")
    
    With ThisWorkbook.Sheets("Test")
    
    'Find unique Condition
    Worksheets("New Data").Range("$B$1:$X$9999").AutoFilter Field:=10, Criteria1:= _
        "Assay by HPLC"
    Worksheets("New Data").Range("$B$1:$X$9999").AutoFilter Field:=15, Criteria1:= _
        "(Average)"
    
    ws.Range("I1:I9999").SpecialCells(xlCellTypeVisible).Copy
    .Cells(1, 27).PasteSpecial

        .Columns("AA:AA").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("AB1"), Unique:=True
        lastCondRow = .Cells(.Rows.Count, "AB").End(xlUp).Row
        uniqueCond = .Range("AB2:AB" & lastCondRow)
        .Columns("AB").ClearContents
        .Columns("AA").ClearContents
    End With

I want to apply this filter as well below the 2 auto-filters under the find unique comment. The column number is F, please help

vba excel
1 Answers

Alternative Option

Offering this alternative, mainly to demonstrate how to apply multiple filters, and one method of applying criteria using wildcards to find exact location of specific characters. This uses a 3 step approach:

• Apply all the filters

• Copy the Data

• Remove duplicates – leaving unique values only

All the action takes place on the “New Data” sheet, and you can change this and the various columns referred to as you wish. Provided for demonstration purposes only.

Option Explicit
Sub testCopyUnique()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("New Data")
Application.ScreenUpdating = False

'Apply all filters and copy the visible cells
With ws.Cells(1, 2).CurrentRegion
    .AutoFilter 10, "Assay by HPLC"
    .AutoFilter 15, "(Average)"
    .AutoFilter 5, "*U???????"  '<~~ ”*” = any number of characters, “?” = one character
    ws.Range("I:I").SpecialCells(xlCellTypeVisible).Copy ws.Range("AA1")
    .AutoFilter
End With

'Remove the duplicates - leaving unique comments only
ws.Range("AA1:AA" & ws.Range("AA" & Rows.Count).End(xlUp).Row).RemoveDuplicates _
Columns:=1, Header:=xlYes

Application.ScreenUpdating = True
End Sub
Admin1
Related