Filter one column based on criteria selected in another cell

[Porcupine_]

Hello,

I have a spreadsheet for data entry. In a hidden column, I have a column for 'Data submitter' (column B) which specifies the name of the person that enters data in that row. I then have a cell (E10) with a drop down selection that allows the data submitter to select their name (using data validation and referencing a hidden list of the names).

I would like to set up the spreadsheet so that when the data submitter selects their name in E10, the sheet automatically filters (or they press a button for it to filter) to the rows that correspond to their name in Column B.

I have tried to make this work using VBA based on some other posts, but it just won't work (sometime with an error message '400' - sometimes no error message).

Is anyone able to help with this?

Thank you,

Porcupine

 

[dssJones]

This code is based on the following assumptions.
1) Your Column B is the ONLY (and FIRST) column to participate in an AutoFilter
2) Your Column B list starts with a label in Row #1
3) The Sheet that we are working with is Sheet1
4) Each time E10 changes the AutoFilter is cleared and "re-Filtered"

Sub setAutoFilter(critVal As String)
With Sheet1.Range("B1")
.AutoFilter

If Sheet1.AutoFilter Is Nothing Then
.AutoFilter
End If

If critVal <> "" Then
.AutoFilter field:=1, Criteria1:=critVal, Operator:=xlAnd
End If
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim criteria As Range
Set criteria = Range("E10")
With criteria
If Target.Address <> .Address Then Exit Sub
If .Value = vbNullString Then Exit Sub
Call setAutoFilter(.Value)
End With
End Sub

[/CODE]