EXCEL macro for filtering more than 2 criterias

[tav1035]

I'm looking to improve the following macro->
Managers are using this to hide everyone that they don't have responsibility for. Unfortunely there isn't a common criteria between his people, so they run this to hide the rows that doesn't have their people on them.
J column contains their names. There are approx 25 people that report to him and sometimes over 500 others on the same spreadsheet.

Columns A - J K L -etc.
WO# NAME HRS STATUS
1542 JON 2.0 CLOSE
1586 BOB 4.2 COMP
1693 DAN 8.0 OPEN
1699 AL 3.0 INP

Sub HidePeople()
Dim X As Integer

For X = 1 To 200

If Cells(X, 10).Value <> "JON" And Cells(X, 10).Value <> "DAN" And Cells(X, 10).Value <> "BOB" Then

     Rows(X).Hidden = True
End If

Next X

End Sub

Any improvements would help.
tav

 

[SkipVought]

Hi,

Add manager to the table. Filter on manager.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[pinkgecko]

I like Skip's idea - an extra column would be the ideal filter. If you can't do that for some reason - and I think updating that new column would be easier than updating th vba every time - then I would use a select case statement, in this fashion. It's more efficient and easier to read:

Sub HidePeople()
Dim X As Integer
For X = 1 To 200
    Select Case Cells(X, 10)
        Case "JON", "DAN", "BOB"
        Case Else
            Rows(X).Hidden = True
    End Select
Next X
End Sub

[blue]When birds fly in the correct formation, they need only exert half the effort. Even in nature, teamwork results in collective laziness.[/blue]

 

[pinkgecko]

For multiple managers, you would have to identify each manager by either name or number

Sub HidePeople()
Dim X As Integer
For X = 1 To 200
    Select Case Cells(X, 10)
        Case "JON", "DAN", "BOB"
           if managerID<>0 then Rows(X).Hidden = True
        Case "BILLY","CHARLIE","GREG"
           if managerID<>1 then Rows(X).Hidden = True
        Case Else
            Rows(X).Hidden = True
    End Select
Next X
End Sub

[blue]When birds fly in the correct formation, they need only exert half the effort. Even in nature, teamwork results in collective laziness.[/blue]

 

[tav1035]

Hi all,
I would love to add manager to the table, however we don't have access to the oracle database.
By editing a macro, It seems like there would be a way to add more than two criterias using the filter, something like so...

Sub FilterMore()
    Selection.AutoFilter Field:=10, Criteria1:="=*JOE*", Operator:=xlOr, _
        Criteria2:="=*BOB*", Operator:=xlOr, _
        Criteria3:="=*TOM*", Operator:=xlOr, _
        Criteria4:="=*JON*"
End Sub

But tried everything to get the syntax correct.
Is it possible?
If not I will use pinkgecko's code.
thanks
tav

 

[SkipVought]

You don't need to do the manager column in Oracle. Use a sheet in your workbook. Then do antother query...

faq68-5829

...within the workbook to JOIN the data from Oracle to your table.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}