Hey man, I've been following your vids since the first and just wanted to say that it's awesome that you keep it up tho you dan't have as many viewers as you deserve! You've been a great help, awesome vids, thank you!
Hello Dan, Thank you very much for this great solution, I just wanted to ask if there is a way to sort the unique items, the combo boxes are filled with, in alphabetic order?
Thank you for the informative video. I'm wondering whether this method would work out on a larger data set (100.000 rows on average) and then combining this with dependent comboboxes. I would probably need multiple array's and fill the arrays based on the selection made in a previous combobox. Not sure whether this will be scalable, I'm afraid it will be too cpu intensive for actual use. Just got an idea, would it be possible to replace the (x, 1) with for instance (x, y) then include another for loop to loop through the columns of which I want the value? Additionally creating a new array with each entry in y?
Danny, when you have time, and as mentioned in your video, would it be possible to demonstrate the code required to sort the combo box listing listing ascending and descending?
Dan, to make the List No more dynamic you can enter a formula. Enter this in A2 and copied down as many rows as you would ever use. If(not(isblank((b2)),rows(a$2:a2),"").
Hey VBAisFUN. Are we able to ask questions here or is there another way? I wanted to know how you can select a range using your mouse. Pressing the macro on selected list and copying only the unique numbers. Then pasting that information in a cell of your choosing. Thank you! Love you stuff.
Thanks for the reply! I have tried as you have suggested but to no avail. I CAN make a macro that pulls from a static place, but I don't want that. I want more flexibility. Lets say I copy a list of data and put it in the clipboard. When I click the macro it pastes the unique list. Is that another way to perhaps achieve my goal? Thank you again!
Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan
Question 1 : How to fill combo box 1 with unique values from column A. G2 type =COUNTIF($A$2:A2,A2) H2 =IF(G2=1,1,0) I2 =SUM($H$2:H2) J2 =A2 K2 =1 L2=IFERROR(VLOOKUP(K2,I:J,2,FALSE),"") drag these down to follow the length of the data. Column K should show 1,2,3,4,5... This will return only the first instance of each item in column L. Use column L to populate the combo box by giving it a name and using the properties box. Hide columns G to L. You could calculate these in a separate tab if needed.
I'm getting some null entries I'm not sure what I'm doing wrong? the first three selections are nulls and the last one is also null, why would this be? I wrote this exactly as I you have it, it does the same with both methods, even with different types of boxes and different spreadsheets
i sure hope you see this... after STOP, my code wont go down to the last part and clear the combobox. it keeps what ever your last selection was . any ideas?
when i do add item , when i click the drop down menu shows only one item. i have to double click on the drop down menu to expand the list. excel sheet xls compatibility mode. any one having the same problem?
in method2, combo-box1 i get runtime error-1004 just after filtering compleeted. if i change the data source to static (cpopy paste of same value with no option to be updated) the error disappears. In both cases the filtering works as well. how can i fix it?? the same code as in video and neme offset as well fltering lines: Dim sortDate As String sortDate = ComboBox1 ActiveWorkbook.ActiveSheet.Range("a1").CurrentRegion.AutoFilter Field:=3, Criteria1:=sortDate offset declaration (in dfferent sheet): =OFFSET(Dates!$A$1,1,0,COUNTA(Dates!$A:$A)-1,1)
Thanks but that was too long and un practical! Like if you were trying to understand what you later wanted to show to your viewers. Too much of "little thingy blah" and not much of real good and structured explanation. 👎
that's exacly what i was looking for! thank you
Hey man, I've been following your vids since the first and just wanted to say that it's awesome that you keep it up tho you dan't have as many viewers as you deserve!
You've been a great help, awesome vids, thank you!
Hello Dan, Thank you very much for this great solution, I just wanted to ask if there is a way to sort the unique items, the combo boxes are filled with, in alphabetic order?
Most helpful! Thanks for the vid!
Ahh, sweet. Thanks krn! great tip. Dan
Thank you for the informative video. I'm wondering whether this method would work out on a larger data set (100.000 rows on average) and then combining this with dependent comboboxes. I would probably need multiple array's and fill the arrays based on the selection made in a previous combobox. Not sure whether this will be scalable, I'm afraid it will be too cpu intensive for actual use.
Just got an idea, would it be possible to replace the (x, 1) with for instance (x, y) then include another for loop to loop through the columns of which I want the value? Additionally creating a new array with each entry in y?
very nice way, and almost solved my problem, Thanks,It's hard to belive it was two years ago.
Glad you liked, Chris!
Danny, when you have time, and as mentioned in your video, would it be possible to demonstrate the code required to sort the combo box listing listing ascending and descending?
Dan, to make the List No more dynamic you can enter a formula. Enter this in A2 and copied down as many rows as you would ever use. If(not(isblank((b2)),rows(a$2:a2),"").
Hey VBAisFUN. Are we able to ask questions here or is there another way? I wanted to know how you can select a range using your mouse. Pressing the macro on selected list and copying only the unique numbers. Then pasting that information in a cell of your choosing.
Thank you! Love you stuff.
you can see an example of what the code looks like if you use the macro recorder in the developer tab. That may help.
Thanks for the reply!
I have tried as you have suggested but to no avail. I CAN make a macro that pulls from a static place, but I don't want that. I want more flexibility.
Lets say I copy a list of data and put it in the clipboard. When I click the macro it pastes the unique list. Is that another way to perhaps achieve my goal?
Thank you again!
Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan
First class work Danny!!!
THANK YOU
Question 1 : How to fill combo box 1 with unique values from column A.
G2 type =COUNTIF($A$2:A2,A2)
H2 =IF(G2=1,1,0)
I2 =SUM($H$2:H2)
J2 =A2
K2 =1
L2=IFERROR(VLOOKUP(K2,I:J,2,FALSE),"")
drag these down to follow the length of the data. Column K should show 1,2,3,4,5...
This will return only the first instance of each item in column L. Use column L to populate the combo box by giving it a name and using the properties box.
Hide columns G to L. You could calculate these in a separate tab if needed.
thank you!
I'm getting some null entries I'm not sure what I'm doing wrong? the first three selections are nulls and the last one is also null, why would this be? I wrote this exactly as I you have it, it does the same with both methods, even with different types of boxes and different spreadsheets
i sure hope you see this... after STOP, my code wont go down to the last part and clear the combobox. it keeps what ever your last selection was . any ideas?
thanks so much!
when i do add item , when i click the drop down menu shows only one item. i have to double click on the drop down menu to expand the list. excel sheet xls compatibility mode. any one having the same problem?
in method2, combo-box1 i get runtime error-1004 just after filtering compleeted. if i change the data source to static (cpopy paste of same value with no option to be updated) the error disappears. In both cases the filtering works as well. how can i fix it??
the same code as in video and neme offset as well
fltering lines:
Dim sortDate As String
sortDate = ComboBox1
ActiveWorkbook.ActiveSheet.Range("a1").CurrentRegion.AutoFilter Field:=3, Criteria1:=sortDate
offset declaration (in dfferent sheet):
=OFFSET(Dates!$A$1,1,0,COUNTA(Dates!$A:$A)-1,1)
Where the hell did you get that Delimiter character from???
the pipe symbol is located above the enter key if you hold shift.
Thanks but that was too long and un practical! Like if you were trying to understand what you later wanted to show to your viewers. Too much of "little thingy blah" and not much of real good and structured explanation. 👎