Include "Starts with" in VB Array

I've got a line in a VB script that currently reads:

ActiveSheet.Range("$A$1:$DD$500").AutoFilter Field:=6, Criteria1:=Array( _ "0a", "1b", "s", "t", "ss"), Operator:=xlFilterValues

Open in new window


I need to add to this line to have it include in the array the existing items plus anything in Field 6 that begins with a "z".  I understand that to determine whether a string starts with "z" I can do this:

Result = String1 Like "z*"

Open in new window


But could somebody explain to me how I could incorporate that result into the array?  Thanks!
Dan CarpIT DirectorAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

pls try
Sub Macro()
Set objDict = CreateObject("Scripting.Dictionary")
For Each c In Range(Range("F1"), Range("F500"))
    If Not objDict.Exists(c.Value) And c Like "z*" Then objDict.Add c.Value, c.Value
Next
strList = Join(objDict.Items, ",") & ",0a,1b,s,t,ss"
aList = Split(strList, ",")
ActiveSheet.Range("$A$1:$DD$500").AutoFilter Field:=6, Criteria1:=aList, Operator:=xlFilterValues
End Sub

Open in new window

Regards
1
 
Dan CarpIT DirectorAuthor Commented:
Thanks Rgonzo1971 - appreciate the clarity of your script.  Will hold onto these steps for future needs.  Cheers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.