combo box, data filter

[ddesantis]

Hi,
I would like to create a data filter (kind of combo box I think).

I want to have this data filter in cell D1 to show unique values (no duplicates) of what is in cell B2:B50000. Besides if I select a value in the filter, I would like to see all the rows associated with that value.

Basically it works exactly as an excel data filter, but it's located in D1 instead of B1.

Example

B2= james
B3=linda
B4=charles
B5=james

in D1 I would like to choose between james, linda, charles (no duplicates)
If I select james, the spreadsheet shows only rows 2 and 5

Is it possible?

 

[WarPigl3t]

	A	B	C	D
1				Data validation:James,Charles,Mary
2		James	10
3		Charles	5
4		James	12
5		Mary	10

<tbody>
</tbody>

I would like to see only line 2 and 4 by selecting James in D1

I got an error: "sub or function not defined"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
firstRow = 2
lastRow = Range("B" & Rows.Count).End(xlUp).Row
i = firstRow
Do Until i > lastRow
     If Range("B" & i).Value = Range("D1").Value Then
            (i).Hidden = False
     Else
          Row(i).Hidden = True
     End If
     i = i + 1
Loop
End Sub

I attached a picture of the file

Also you messed up where it says (i).Hidden = False. It should say Row(i).Hidden = False

 

[VoG]

I think it should be

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
firstRow = 2
lastRow = Range("B" & Rows.Count).End(xlUp).Row
i = firstRow
Do Until i > lastRow
     If Range("B" & i).Value = Range("D1").Value Then
            Rows(i).Hidden = False
     Else
          Rows(i).Hidden = True
     End If
     i = i + 1
Loop
End Sub

 

[WarPigl3t]

I'm going to bed now. So if you run into anymore problems, press F8 while you are looking at the code. This will run the code line by line. You'll see a yellow backround over the code currently being evaluated. Each time you press F8, a new line of code gets evaluated. Place your curser over the code that has been processed and check to make sure the output of each line of code is what you expected it to be. So if you put your curser over "lastRow", it will tell you what the last row is. That is how you troubleshoot.

 

[ddesantis]

thanks a million! it works