Extract Data to another Column

[hoodielive]

Is there a way to extract all values starting with aa, pt, ra or whatever multiple values starting with defined prefixes into another column.

So if
aa12345
as12345
root
sys
aa90231

are in column a
I would like to move them into column F or possibly another worksheet.
But I would need to extract the entire row or by columns ??

I would also have to be able to pass multiple parameters into the code -- such as aa, ab, ac and it's proceeding numbers. I hope this clear.

Please Help

 

[sriram170]

hi

assuming data starts from a1 , use the below autofilter option to do the rest of the action after filtered
ActiveSheet.Range("$A$1).AutoFilter Field:=1, Criteria1:="=aa*", Operator:=xlAnd

 

[hoodielive]

First of all, I would like to express my gratitude to you sriram170. I can not begin to tell you how hard I've been working to automate certain processes at my job. The other part of this response is to seek clarity as I am getting an error when I run the code. It says it expected a ) or separator. Also, I will be using multiple prefixes, not just aa. So in that part where the criteria =aa* do I also put & =ab* & =fa*? Thank you for your kindness and your willingness to help me on this.

 

[sriram170]

then u should use for loop in a particular range then give the criteria with each cell in range to filter

 

[hoodielive]

How? This is why I posted the question. I am a beginner at VBA. I've watched so many tutorials but it is not clicking.

 

[hoodielive]

Option Base 1
Option Explicit
Sub cleansewithArrays()

Dim filter() As Variant
' filter is the array with the prefixes I will need to autofilter
' What I want to remain is only values with the following prefixes
filter = Array( _
"fe*", "ff*", "fw*", "ll*", "pg*", "ph*", "pm*", "pp*" _
, "pt*", "xx*", "zx*")
ActiveSheet.Range("A:A").AutoFilter _
Field:=1, Criteria1:=Array(filter), Operator:=xlOr ' xlFilterValues

End Sub


Here's what I came up with. However it only filters the last criteria : zx. What am I doing wrong?