Hide rows based on a selection from a combo box
Results 1 to 4 of 4

Thread: Hide rows based on a selection from a combo box
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Hide rows based on a selection from a combo box

    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!

    A B C
    row 1 473 This is data for 473 Hide this row
    row 2 998 relates to 473 Hide this row
    row 3 356 This is data for 356
    row 4 473 This is data for 473 Hide this row
    row 5 473 This is data for 473 Hide this row
    row 6 298 This is data for 298
    row 7 298 This is data for 298

  2. #2
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    7,447
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide rows based on a selection from a combo box

    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
    A B C D
    1 Data1 Data2 Data3 Data4
    2 row 1 473 This is data for 473 Hide this row
    3 row 2 998 relates to 473 Hide this row
    4 row 3 356 This is data for 356
    5 row 4 473 This is data for 473 Hide this row
    6 row 5 473 This is data for 473 Hide this row
    7 row 6 298 This is data for 298
    8 row 7 298 This is data for 298
    Sheet1




    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 by jim may; Aug 2nd, 2014 at 10:59 AM.
    To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie

  3. #3
    New Member
    Join Date
    Jul 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide rows based on a selection from a combo box

    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

  4. #4
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    7,447
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide rows based on a selection from a combo box

    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
    To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •