Filtering data VBA with 3 criteria

[cortexnotion]

Good Morning,

I stumbled across this forum through some research and I was hoping you could help me with some VBA code.

I have a data set A4:P7000 (row 3 is Headers) which I have set up as an array. I need to remove the following things from the data before copying the matching rows to the new workbook in Sheet1 from cell A2. The curveball is I need to copy the row in reverse order leading with Column P to A. My string criteria is:

- Remove any values from Column F equal 0
- Remove any values from Column A and Column B that match the names in the NamesArray anywhere in the string (wildcard?) and case not to be sensitive

I've had a go setting my arrays and starting my code but not sure on how to compare the range array with the name array and factor in my wildcard need. I'm also not sure how to copy the data to the new workbook.

Option Compare Text

Sub myarray()

Dim sh1 As Worksheet, wb2 as Workbook, sh2 as Worksheet
Dim dataarray As Variant, namesarray As Variant,
Dim i As Long

Set sh1 = Worksheets("RawData")
Set wb2 = Workbooks.Open("C:\Userx\tempwork\new.xlsm")

dataarray = Range("A4:P7000").Value
namesarray = Array("JOHN","CLAIRE","PETER","MICHELLE","PAUL","CHRIS")

For i = LBound(TheArray) To UBound(TheArray)
If RangeArray(i, 6) > 0 And _
   RangeArray(i, 10) Like ??? 'How to perform comparison with namesarray?

Many thanks

Chris.

 

[cortexnotion]

The speed of the code is amazing, thank you!

I hadn't clocked that section of code needed to be = 0 to copy >0. Last question I promise (haha) is there a may to turn this section to a positive statement of the conditions?

For i = 1 To UBound(a)    e1 = True
    If a(i, 2) Like "*W*" Or a(i, 14) > 0 Then
      e1 = False
    Else

 

[DanteAmor]

The speed of the code is amazing, thank you!

I hadn't clocked that section of code needed to be = 0 to copy >0. Last question I promise (haha) is there a may to turn this section to a positive statement of the conditions?

For i = 1 To UBound(a)    e1 = True
    If a(i, 2) Like "*W*" Or a(i, 14) > 0 Then
      e1 = False
    Else

You can explain it with examples.
Which ones you want to copy and which ones you don't want to copy.

 

[cortexnotion]

Typo there... With:

For i = 1 To UBound(a)    e1 = True
    If a(i, 2) Like "*W*" Or a(i, 14) = 0 Then
      e1 = False
    Else

I'd like to match >0 and contains *W*?

Thanks, Chris

 

[DanteAmor]

Which ones you want to copy and which ones you don't want to copy.

I'd like to match >0 and contains *W*?

Please explain with examples, which ones to copy and which ones not to copy.

 

[cortexnotion]

B	C	D	N	Copy?
001/W1000	FEASIBILITY	COSTINGS	15	No
001/W1000	REFURB	SCIENCE	0	No
001/W1000	MOTOR	HOME	50	Yes
001/S1000	GARAGE	STONE	20	No
001/W1000	BAG	PAPER	40	Yes
001/W1000	COAT	CLIP	99	Yes
001/S1000	DESK	METER	10	No
001/W1000	CAN	METAL	55	Yes
001/W1000	TIN	PIPE	0	No

 

[cortexnotion]

- Column B contains "W" anywhere in string
- Column C & D does NOT contain any words in namesarray anywhere in string
- Column N greater than 0

Thanks!

 

[FatBoyClam]

Not wanting to steal Dante's thunder, but

For i = 1 To UBound(a)
    e1 = True
    If Not a(i, 2) Like "*W*" Or a(i, 14) = 0 Then
      e1 = False
    Else

Should do the trick

 

[DanteAmor]

is there a may to turn this section to a positive statement of the conditions?

I'd like to match >0 and contains *W*?

- Column C & D does NOT contain any words in namesarray anywhere in string
- Column B contains "W" anywhere in string
- Column N greater than 0

To see the positive statement, it could be like this:
Use the following complete code.

Option Explicit

Option Compare Text

Sub myarray()
  Dim wb2 As Workbook, a As Variant, b As Variant, namesarray As Variant
  Dim i As Long, x As Integer, j As Long, k As Long, n As Long, e1 As Boolean
  Dim lr As Long
  
  lr = Sheets("ALL DATA").Range("B" & Rows.Count).End(xlUp).Row
  a = Sheets("ALL DATA").Range("A4:P" & lr).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  namesarray = Array("COST", "FEAS", "REF", "RRFB", "DUMMY", "SPARE", "DMA", "LOG", "METER", "CONSULT", "PIPE")
  k = 1
  Set wb2 = Workbooks.Open("S:\Operations\BP&P\2297\Asset Data Management\Asset Data Delivery Water\Capitalisation Tracker\Water Tracker\Water.xlsx")
  
  For i = 1 To UBound(a)
    e1 = True
    For x = LBound(namesarray) To UBound(namesarray)
      If a(i, 3) & a(i, 4) Like "*" & namesarray(x) & "*" Then e1 = False
    Next x
    If e1 = True And a(i, 2) Like "*W*" And a(i, 14) > 0 Then
    Else
      e1 = False
    End If
    If e1 = True Then
      n = 1
      For j = UBound(a, 2) To 1 Step -1
        b(k, n) = a(i, j)
        n = n + 1
      Next j
      k = k + 1
    End If
  Next i
  
  wb2.Sheets("Sheet1").Range("A2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

 

[cortexnotion]

Many thanks both, all good on the criteria. Before I depart and implement all this, if I wanted to post the results from as they appear A-P what would I change? Cheers

 

[DanteAmor]

Many thanks both, all good on the criteria. Before I depart and implement all this, if I wanted to post the results from as they appear A-P what would I change?

I do not understand.
You currently have A-P and the macro writes P-A, now what do you need A-P writes A-P?

Change this line
For j = UBound(a, 2) To 1 Step -1

By this:
For j = 1 To UBound(a, 2)