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?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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 file
 
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,972
Members
430,099
Latest member
rdhoy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top