Hide rows based on a selection from a combo box

scottmcclean

New Member
Joined
Jul 2, 2014
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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>
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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
Back
Top