Hide rows based on a selection from a combo box

scottmcclean

New Member
Joined
Jul 2, 2014
Messages
23
Hi all,
I would like some help please in being able to hide rows based on a combobox selection.

I have a sheet that contains a lot of rows an would like the user to be able to filter out rows depending on what they select from a combobox. I am new to VBA and have looked at some examples on the net but none seem to do what I would like.

Here is an example:
The combobox is called combobox 1 which sits on the same sheet as the data (sheet1). This holds a number of values. These values correspond to vales that could be anywhere within each row. Most though are in column B. If there is a solution to finding the value anywhere in the row then excellent, if not I could just try and put all the values in one column. Whatever the user selects from the drop down I would like the rows with that value in to be hidden, for instance "473".

Any help gratefully received!

ABC

<tbody>
</tbody>
row 1473This is data for 473Hide this row
row 2998relates to 473Hide this row
row 3356This is data for 356
row 4473This is data for 473Hide this row
row 5473This is data for 473Hide this row
row 6298This is data for 298
row 7298This is data for 298

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
This is not exactly what you want -- I've used an InputBox instead of a Combobox, but
After Applying the Autofilter (drop-downs) to your data;;; Run the below Macro "Foo"

Excel 2012
ABCD
1Data1Data2Data3Data4
2row 1473This is data for 473Hide this row
3row 2998relates to 473Hide this row
4row 3356This is data for 356
5row 4473This is data for 473Hide this row
6row 5473This is data for 473Hide this row
7row 6298This is data for 298
8row 7298This is data for 298

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>
Sheet1




Rich (BB code):
'Change Range ref below to suit///
Sub Foo()
Ans = InputBox("Enter the Number you wish to hide the rows of") + 0
    ActiveSheet.Range("A1").AutoFilter Field:=2, Criteria1:="<>" & Ans & "", _
        Operator:=xlAnd
    ActiveSheet.Range("A1").AutoFilter Field:=3, Criteria1:="<>*" & Ans & "*", _
        Operator:=xlAnd
End Sub
 
Last edited:

scottmcclean

New Member
Joined
Jul 2, 2014
Messages
23
Hi Jim,
Thank you very much for taking the time to help me. The solution works, and has definitely helped.

Just out of interest, is using a combobox to do this very difficult?

Many thanks again!

Scott
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
It's probably just a matter of passing the combobox1 value (selected), somewhat like

Ans = combobox1.value

But I'm not fully acquainted.. You could try it.. Jim
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,102
Messages
5,509,246
Members
408,718
Latest member
Bea2136

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top