combo box, data filter

ddesantis

New Member
Joined
Aug 30, 2014
Messages
14
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?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
Yes it is possible. First I'd like to call your combo box a drop down list because I'm hard core like that. Now in order for your drop down list to have unique values, you need to have a list of unique names somewhere on your excel file. I would recommend putting unique names in a seperate worksheet but you can put it in the same worksheet if you want. You can type the unique names manually for now. Later you can ask someone to help you create a formula to search your B column and return unique names to your list on your other worksheet that the combo box will be taking the names from. That's a big problem on it's own and I am too tired to get into it. I will however help you with displaying the data.

Assuming your drop down list is in D1:
Code:
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
          Row(i).Hidden = False
     Else
          Row(i).Hidden = True
     End If
     i = i + 1
Loop
 

ddesantis

New Member
Joined
Aug 30, 2014
Messages
14
Hi WarPiglet,

Thanks For your quick reply, but I need to clarify a couple of points
- do I need to create a combo box with the values in D1?
- I have my unique list of names in a separate sheet (sheet2) range A4:A6. How should I change the code?
- do I need Option Explicit at the beginning?

thanks
 

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
Oh good you already have your list of unique names somewhere. Select cell D1. Then click on the Data tab at the top. Find where it says Data Validation. In the dialog box that appears, you will see the first criteria says "Any Value" Change that to "List". Then at the bottom of the dialog box you can designate the range of your list. So designate it as the unique names. Now you have a drop down list in cell D1. As for option explicit, I have no idea what that even does. You don't need it.

Here's what to do with the code I gave you. In the worksheet that has the drop down list, right click on the sheet name at the bottom. Select "View Code" from the menu. Then designate the code to be an "On Change Event". Then paste the code inside of there. This way the code will run every time you make a change to the worksheet such as when you change the value in the drop down list.
 
Last edited:

ddesantis

New Member
Joined
Aug 30, 2014
Messages
14

ADVERTISEMENT

Sorry to bother you. But how do I designate the code to be an "On Change Event"? I'm not familiar at all with VBA
 

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
Oh that's easy. Well first right click on the tab that is your sheet name. Select "View Code" from the menu that appears. Now you are in the VBA of that worksheet. At the top you will see 2 drop down boxes. I don't remember which one, but click on each and a drop down list will appear. In the drop down list you will find where it says "On Change" or "On Change Event". I don't remember exactly. Once you click on the On Change, code will appear. You know how you can type this...
Sub myMacro()
End Sub
Well it's kinda like that. Only it will say something like...
On_Change_Event()
End Sub
Then just paste my code inside of there.
Watch this youtube video. www.youtube.com/watch?v=u9-Xb-5bSZU
Ignore everything he says because he talks too much. Just focus your attention on the 2 drop down lists he clicked on and what he selected from the 2 drop down lists.
 

ddesantis

New Member
Joined
Aug 30, 2014
Messages
14

ADVERTISEMENT

This is how it looks like, but it doesnt work


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

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
It doesn't work because you modified my original code.
Code:
[FONT=Verdana]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[/FONT][SIZE=2]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
          Row(i).Hidden = False
     Else
          Row(i).Hidden = True
     End If
     i = i + 1
Loop
[/SIZE][FONT=Verdana]End Sub[/FONT]

 

ddesantis

New Member
Joined
Aug 30, 2014
Messages
14
ABCD
1Data validation:James,Charles,Mary
2James10
3Charles5
4James12
5Mary10

<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"

Code:
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 fileC:\Users\2003051\Desktop
 
Last edited:

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
It didn't work because you do not have anything in cell B1. The code for lastRow is determined by the first blank cell in column B. B1 should not be blank so that the code can look for the first blank cell in column B which should also be the last row. That is most likely why you have an error. I see nothing else wrong with the code. Why is B1 blank? If you are using that cell to put header in, then put the header in so the code will run. Otherwise, move your dataset up to B1. There should be no reason B1 is blank.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,880
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top