VBA Filtering with multiple criteria

[Jvalades]

I am trying to create a filter that will select 4 different parts.

another constraint is: Sometimes a part will be added with a similar name so I cannot specify the exact name.

So what I started with is:

ActiveSheet.Range("$A$1:$N$6000").AutoFilter Field:=4, Criteria1:="=*A717*", _
Operator:=xlOr, Criteria2:="=*XBEA*"

Can I add more criteria to this formula. Right now this works for finding the two parts.

Best

 

[Jvalades]

M.

If you are editing your list to just include 1 item would that effect how it is coded?

Ex.

'Create named range MyList
    .Names.Add Name:="MyList4", RefersTo:="='Orders'![B]$AC3[/B]"

 

[Marcelo Branco]

Do not understand your question. Please, elaborate.

M.

 

[Jvalades]

Do not understand your question. Please, elaborate.

M.

Well lets make it simple.

Can you find any problems with this code?

'creating a Advanced filter to find A30105    With Sheets("Orders")
        'Insert values of interest in cells AA3
        .Range("AA3").Value = "A30105"
        
    'Create named range MyList2
    .Names.Add Name:="MyList2", RefersTo:="='Orders'!$AA$3"
    
    'Put a header in AA2
    .Range("AA2").Value = "MyList2"
     
     'Create the criteria range in range Y2:Y3 using a formula
        .Range("Y2").Value = "Formula"
        .Range("Y3").Formula = "=OR(INDEX(ISNUMBER(SEARCH(MyList2,D2)),0))"
        
        'Apply advanced filter in data range
        .Range("$D$1:$D$" & .Cells(.Rows.Count, "D").End(xlUp).Row). _
            AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range("Y2:Y3"), _
            Unique:=False


    End With

 

[Marcelo Branco]

If the List2 has just one value the original formula doesn't work

Change the code line that inserts the formula to

.Range("Y3").Formula = "=ISNUMBER(SEARCH(MyList2,D2))"

M.

 

[Jvalades]

If the List2 has just one value the original formula doesn't work

Change the code line that inserts the formula to

.Range("Y3").Formula = "=ISNUMBER(SEARCH(MyList2,D2))"

M.

Ahh I figured it was something with that. I tried using just IS but didn't have the rest.

Thanks.

 

[Jvalades]

M.

Can I trouble you with another question.

So after I have done all my filtering I want to find a location of the next cell after my header. If it filters out rows 2 through 5 than 6 would be the next after the header.

I have been using

Columns("I:I").Select    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop

Can I store that cell reverence?

Because after that I needed to copy that formula all the way down. I've been using this code for that.

'copy formula by row count       
  Lr = Range("D" & Rows.Count).End(xlUp).Row
        Range("I2").Copy Destination:=Range("I2:I" & Lr)

But... Range("I2") is wrong. it needs to be the saved cell reference.

Thanks.

 

[Jvalades]

This ended up working

 SortCell = ActiveCell.Address
    Range(SortCell).FormulaR1C1 = "=RC[-2]&""-""&RC[-1]"
    
    'copy formula by row count
        Lr = Range("D" & Rows.Count).End(xlUp).Row
        Range(SortCell).Copy Destination:=Range("I2:I" & Lr)

 

[Marcelo Branco]

Glad you solved the issue

M.

 

[Jvalades]

Glad you solved the issue

M.

M.

Can I loop this code to apply to multiple sheets. So I do not have to redefine variables for each sheet.

After I filter out the data I do not need I want to delete it from my excel file. So I can apply other filters in the future.

This is the code I am using.

Dim oRow As Range, rng As Range    Dim myRows As Range
    With Sheets(2)
        Set myRows = Intersect(.Range("A:A").EntireRow, .UsedRange)
        If myRows Is Nothing Then Exit Sub
    End With


    For Each oRow In myRows.Columns(1).Cells
        If oRow.EntireRow.Hidden Then
            If rng Is Nothing Then
                Set rng = oRow
            Else
                Set rng = Union(rng, oRow)
            End If
        End If
    Next


    If Not rng Is Nothing Then rng.EntireRow.Delete

Thanks

 

[Marcelo Branco]

These are different questions. I suggest you create a new Thread - so others members can also try to help you.

M.