Another approach using dictionary [code] Private Sub UserForm_Initialize() Dim arr As Variant arr = GetDistinct(Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)) ListBox1.List =...
Thanks a lot PNRao for this great solution Another approach using collection [code] Private Sub UserForm_Initialize() Dim ws As Worksheet...
Thanks vaali... this is the data sheet1 data EID NAME PAY PayDay 14123 Joe1 $1,234.00 29-Jun-17 14124 Joe2...
HI, Here is the required macro to search a field and if value found copy the whole row into another...
I found a solution like this :Records in between two specific dates easily can be filtered using drop-down lists ....
I found a solution as follows: Watch the video to see how it's done and download example file : https://youtu.be/SZduAoOtiS0
I have sorted the above problem. Code is given below... [code] Sub seperate() Call CleanSheets MsgBox "1 of 10 Cleaning...
I want the user to be able to sort a field by choosing the column title from a combobox. I have a vertical list of the column titles and the associated number (0-16) in an array that I can access with vlookup and can access the header with hlookup, but can’t get the column designation into the sortkey of the macro
Here is the set of instructions to sort data based on header selection from the combobox using VBA
1. Go to VBA editior by clicking on Alt+F11.
2. Insert Label , Combo box and command button.
3.Change the cation of the label to “Select Header to Sort Data”.
4.Change the caption of the CommandButton to “Sort Data”.
5. Here is the design of the screen shot for your reference.
6. Double click on the Userform and add the following code.
Private Sub UserForm_Initialize()
'Populate Color combo box.
Dim rngHdr As Range
Dim ws As Worksheet
Dim HdrRange As Range
Set HdrRange = Range("A1:Z1")
Set ws = Sheets("Sheet1")
For Each rngHdr In HdrRange
7. Double click on the command button and add the following code.
Private Sub CommandButton1_Click()
'Here Range("A1:E20") is target range to sort
KeyToSort = ComboBox1.Text
Sheets("Sheet1").Range("A1:F20").Sort Key1:=KeyToSort, Header:=xlYes
8. Go to worksheet, Insert shape from the illustration group.
9. Right click on the UserForm, click on edit text as “Show Userform”.
Here is the screen shot for your reference.
11. Right click on the shape, click on assign macro.
12. Click on ‘New’ from Assign Macro window and add the following code.
13. Now, click on the ‘Show userform’ from the Worksheet.
14. Select header from the ComboBox and click on Sort data .
15. You can see the output on the worksheet.
Example: I want to sort data based on 2nd column. I have selected Hdr2 from the ComboBox and clicked on Sort Data button.
Now, you can see the following output. data has sorted based on 2nd column.
Hope, It clarifies your query.
You can find related information in the specified link for more information.
VBA Sort Data with headers Excel Example Macro Code
Thanks for your response. I’m new to VBA but not to Excel so in the interim I came across the following solution. (I should add that the data populating the table is based on formulae from other sheets in the book and so is protected and the macro unprotects and reprotects the worksheet).
I used a combo box to list the column headers vertically and gave each its corresponding number and its cell address in adjacent cells. Then used the Combo box output cell to define the cell address via a vlookup function. A button then starts the macro which reads that address as a variable and inserts as the rangekey. I like the idea of a single box instead of a combo box and then a button and I ma going to experiment with your code to help me learn more about VBA.
Please share using the share button above.
If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.
Found the answer useful and wants to credit the user, then vote the answer (vote up).