Working with array to filter

[quitethenovice]

Hello my friends

I'm still quite new to VBA and im trying to use array to sort my large Sheet1 quickly rather than normal autofilter. Could you help me with how I move the data into the output array and in the order I have listed.

Your community guidance valued.

Dim Input as Variant, Output As Variant
Dim ArrIN As Variant, ArrOUT As Variant, LastRow As Long, x As Long, y As Long

LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Input = Sheets("Sheet1").Range("A2:T" & LR1).Value

ReDim Output(1 To LastRow, 1 To 10)
x = 1

For x = LBound(Input) To UBound(Input)
If Input(x, 3) > 0 And Input(x, 6) <> "" And Input(x, 8) Like "*Free*" Then
End If

'Output Columns in order 5,6.7,1,2,3,8,9,10,3,4

Sheets("Sheet2)".Range("A2:J2").Resize(UBound(Ouput)).Value = Output

 

[quitethenovice]

Apologies friend, code was draft version, here is proper....

Dim Input as Variant, Output As Variant
Dim LastRow As Long, x As Long, y As Long

LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Input = Sheets("Sheet1").Range("A2:T" & LastRow).Value

ReDim Output(1 To LastRow, 1 To 10)
x = 1

For x = LBound(Input) To UBound(Input)
If Input(x, 3) > 0 And Input(x, 6) <> "" And Input(x, 8) Like "*Free*" Then
End If

'Output Columns in order 5,6.7,1,2,3,8,9,10,3,4

Sheets("Sheet2)".Range("A2:J2").Resize(UBound(Ouput)).Value = Output

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub quitethenovice()
   Dim InAry As Variant, OutAry As Variant, ColAry As Variant
   Dim LastRow As Long, x As Long, y As Long, r As Long
   
   LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
   InAry = Sheets("Sheet1").Range("A2:T" & LastRow).Value
   ColAry = Array(5, 6, 7, 1, 2, 3, 8, 9, 10, 3, 4)
   ReDim OutAry(1 To LastRow, 1 To 10)
   
   
   For x = LBound(InAry) To UBound(InAry)
      If Input(x, 3) > 0 And Input(x, 6) <> "" And Input(x, 8) Like "*Free*" Then
      r = r + 1
      For y = 0 To UBound(ColAry)
         OutAry(r, y + 1) = InAry(x, ColAry(y))
      Next y
   End If
   
   'Output Columns in order 5,6.7,1,2,3,8,9,10,3,4
   
   Sheets("Sheet2").Range("A2").Resize(UBound(OutAry), 10).Value = OutAry
End Sub

 

[quitethenovice]

@Fluff, thank you sir. I changed Input to InAry and added Next I After End If and it works well.

May I ask one more development idea. If I had a need to also search for an array of phrases such as:

WordsAry = Array("Red", "Green", "Yellow", "Blue", "Black", "Brown")

This would not work so how would I search the array too....

If InAry(x, 3) > 0 And InAry(x, 6) <> "" And InAry(x, 8) Like "*Free*" And InStr(InAry(x, 9), WordsAry > 0 Then

 

[Fluff]

Would InAry(x, 9) just have one of those words on it's own, or would it have a phrases that contains one of the words?

 

[quitethenovice]

Hello mr fluff, it would be a single word on its own

 

[Fluff]

In that case try

Wordstr = "RedGreenYellowBlueBlackBrown"

If InAry(x, 3) > 0 And InAry(x, 6) <> "" And InAry(x, 8) Like "*Free*" And InStr(1, Wordstr, InAry(x, 9), 1)>0 Then

 

[quitethenovice]

Thank you for all your help

 

[Fluff]

You're welcome & thanks for the feedback.