vba: drop-down list data changes the number of rows RRS feed

  • Question

  • Hello!

    I admit that I am dummie in the VBA. But unfortunatelly I need to use it in my work.

    My question is:

    I have a table and I need to make a drop-down list for the columes in which, when you chose different data, it changes the number of the rest of the rows. for ex.: in A2 I chose the value X and the rest of the collums have only the row2. But if I chose the value Y, all the rest columes have 2 or 3 rows, etc. I add the schematic example.

    I hope you understood what i tried to ask.. :) I would realy appreaciate the help (in code especially).


    thank you !



    Monday, June 4, 2012 9:48 AM

All replies

  • That is already built into Excel - select your table and apply a data filter. Cell A1 will get a dropdown that has X and Y (and any other unique values in column A) as choosable values, and choosing either will show only those values for the entire table.

    If cell A4 is truly empty, choose Column A, use goto / special cells blank, type =  then press the up arrow key once, and press Ctrl-enter. Then copy column A and paste special values - that will fill in all the blanks to allow filtering to work properly.

    HTH, Bernie

    Monday, June 4, 2012 1:13 PM
  • thank you for the answer!

    unfortunatelly that's not what i was asking.. sorry, i didn't managed to explain properly ..:)

    i'm trying once again.

    the file that i am trying to create, it is somewhat an systeme where you fill the orders.

    so when you have a drop-list (that you can chose a type of your order in each cellule in the columne A, starting with the A2), you chose a type of your order. Regarding what type of order you chose, it changes the the rest of possibilities of your choise. Some orders need to have just one line to fill (in example the X value), others need to have two or more lines (in ex. the value Y). What i want to do here that the choice in the columne A would change the number of lines to fill.

    hope i was more clear right now.. :) thank you in advance!

    Tuesday, June 5, 2012 8:33 AM
  • Sorry for the confusion.  Try this:


    HTH, Bernie

    Tuesday, June 5, 2012 1:06 PM
  • thank you, i'll try  :)!
    Wednesday, June 6, 2012 10:19 AM