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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,914
Messages
5,483,689
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top