Using Autofilter in VBA for More than One Column and OR operator? 1

[shelby55]

Hello

I have a worksheet with data in it and I would like to extract the applicable rows when the criteria of anything being in Column I OR anything being in Column J OR anything being in Column K.

The code below doesn't yield any cases because I assume it is looking for when all 3 criteria are met and there are zero cases where all 3 meet. I tested it by adding something in all columns for a few rows and they were picked up. So how do I get this to be "or" instead of "and":

Sub Triage_Data()

Dim ws As Worksheet

If worksheetexists("Triage_Data") Then
MsgBox "Worksheet already exists - need to delete it first"
Else


With Sheets("HDM_Values")
.AutoFilterMode = False
With .Range("A2:AA2")
.AutoFilter
.AutoFilter Field:=9, Criteria1:="<>"
.AutoFilter Field:=10, Criteria1:="<>"
.AutoFilter Field:=11, Criteria1:="<>"

End With
.AutoFilter.Range.Copy Destination:= _
Sheets.Add.Cells(Rows.Count, 1).End(xlUp)(2, 1)
.AutoFilterMode = False
End With
Set ws = ActiveSheet
ws.Name = "Triage_Data"
End If
End Sub

Thanks for any and all assistance.

 

[SkipVought]

Hi,

Can't do AND criteria as you have experienced.

So what can be done is a query via MA Query, using 3 queries, each with itsown criteria something like this

Select *
From [YourSheet$]
Where Fld1='val1'

Union All

Select *
From [YourSheet$]
Where Fld2='val2'

Union All

Select *
From [YourSheet$]
Where Fld3='val3'

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

I'd use a helper column which is the concatenation of your 3 columns and then autofilter on it.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

You could also try Advanced Filter in place.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Thanks for the super quick responses.

Is it possible to, using VBA, create a helper column looking for the 3 criteria? And then use it to filter?

If not, because the worksheet of raw data is produced in other software, I'll get that report to create the "helper columns" and use those to filter the data.

Thanks again.

 

[PHV]

A starting point:

Dim lngLastRow As Long
With Sheets("HDM_Values")
  lngLastRow = .UsedRange.Rows.Count + 1
  .Columns(1).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  With .Range("A2")
    .FormulaR1C1 = "=CONCATENATE(RC[9],RC[10],RC[11])"
    .AutoFill Destination:=.Range("A1:A" & lngLastRow), Type:=xlFillDefault
  End With
  With .Range("A2:AB2")
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:="<>"
  End With
  .AutoFilter.Range.Copy Destination:=Sheets.Add.Cells(Rows.Count, 1).End(xlUp)(2, 1)
  .Columns(1).Delete
  .AutoFilterMode = False
End With
Set ws = ActiveSheet
ws.Name = "Triage_Data"
ws.Columns(1).Delete

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Thanks so much PHV, it worked brilliantly!!