# Auto sorting Drop Down lists in Excel



## div163 (Jun 15, 2011)

hey guys, 
I have an excel sheet with two dropdown list in column A and in Column B.

The dropdown in column B is dependent on value of cell in column A.

Now i want to autosort my data whenever user changes value in column A. Also i want to put all empty rows at the end of the table.

I am attaching the excel file here for your easy reference. Hope someone will help me.


----------



## div163 (Jun 15, 2011)

Now i got the code to use for sorting but i don't know how to link it up with change from dropdown list.
By the way the code which works for me is as follows:
Sub DoSort()
ActiveWorkbook.Worksheets("Chemical Prices").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Chemical Prices").Sort.SortFields.Add Key:=Range( _
"A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Chemical Prices").Sort
.SetRange Range("A240")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Can anyone please help me for linking it with dropdown value change or at least cell value change would also be ok.


----------



## RSpecianjr (Jan 20, 2010)

Hey div163,

You will want to use an event macro. When you have the VBA editor open, double click on your worksheet in the left pane. This will pull up the VBA page for that particular worksheet. There will be two dropdowns at the top. Select Worksheet from the left dropdown and Change from the right.

Paste your code, without the Sub and End Sub lines, into the Private Sub it created.

Whenever there is a change on the worksheet, it will run your macro. If you want to make it only run if a particular cell is changed, you will have to add something like:

if target.address <> "$A$2" then exit sub

Put this as the first line under Private Sub and before your script.

I haven't tested it.

Hope it helps,

Regards,

Robert D Specian Jr


----------



## div163 (Jun 15, 2011)

Also any suggestion to improve readability, presentation or formulas used are always welcome.


----------



## div163 (Jun 15, 2011)

Hello RSpecianjr,
your idea works perfectly. Very very thanks to you. I am very grateful to you.


----------



## div163 (Jun 15, 2011)

Hmmm, the code and event works but not the way i wanted. There are two main problems. First that autosort should be done when user changes value from dropdown only and the second is i want that macro to run after user changes value from both the dropdowns. i dont want that it auto sorts when user just changes one value and then he/she have to look the whole data for changing other value. Can any one help in that....

RSpecianjr, i have big hopes from you..


----------



## RSpecianjr (Jan 20, 2010)

Hey div,

I'm not sure what you are asking...

1. You want to run the macro only when the dropdown (A2) is changed? That should be what it is. If not, can you post your code?

2. You don't want the above, but when both dropdowns are changed (A2 and B2), you want it to autosort?

Why not just change it to run when you change B2 instead. You limit what you can select from that dropdown, so if you change A2, you have to change B2 anyway.

Also, for readability, I would have it three different worksheets. Just move the other two charts to their own worksheets.

Another note, stay away from Merged cells. Although they are easy to use and look good, from a layout perspective, it is better to use Center Across Selection. This will come in handy when you start getting into more VBA, because Merged Cells are a pain with VBA.

Regards,

Robert D. Specian Jr.


----------



## div163 (Jun 15, 2011)

Thank you very much Robert. I would do everything you suggests. I just want to make a good user friendly project and you are very much help into that. Thank you. Thank you very much.


----------

