CODEWITHSUNDEEP
vbaexcelnewline
Arun Thomas
Arun Thomas

Reputation: 845

AutoFilter for a value which has a newline character in between (Excel/VBA)

I have written a VBA code where i have to filter a set of data. But some of my data have a newline character in it and i want to filter those values. I tried using vbCrLf, but that didn't work.

Here is my code:

ActiveSheet.Range("$A$1:$M$10").AutoFilter Field:=2, Criteria1:="=1234 -" & vbCrLf & "product"

Here i am filtering for the value 1234 -product (in between the '-' and 'product' there is a newline charater) in the second column. But the above code does not filter anything.

What am I doing wrong here ?

Upvotes: 2

Views: 716

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

You can use the following strings for Criteria1:

"=1234 -?product"
"=1234 -*product"
"=1234 -" & vbLf & "product"

Options 1 and 2 use wildcards - you can even drop product off the end if you use *. Option 3 is the only option you didn't try per the comments ;)

All give the expected output for me:

Before:

enter image description here

After:

enter image description here

Sample code:

Option Explicit

Sub Test()

    Dim ws As Worksheet
    Dim rng As Range
    Dim strCriteria As String

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("A1:A4")

    'unset filter
    ws.AutoFilterMode = False

    'set filter - test 1 with wildcard
    strCriteria = "=1234 -?product"
    rng.AutoFilter Field:=1, Criteria1:=strCriteria

    'unset filter
    ws.AutoFilterMode = False

    'set filter - test 2 with wildcard
    strCriteria = "=1234 -*product"
    rng.AutoFilter Field:=1, Criteria1:=strCriteria

    'unset filter
    ws.AutoFilterMode = False

    'set filter - test 3 with vbLf
    strCriteria = "=1234 -" & vbLf & "product"
    rng.AutoFilter Field:=1, Criteria1:=strCriteria

End Sub

Upvotes: 1

Related Questions