Streamline VBA code (sorting by 3 different columns)
Experts:
I need some help with streamlining (i.e., potentially merging three functions into one) some VBA code.
Background: I have three command buttons (i.e., "Sort By Column B", ""Sort By Column C", "Sort By Column D") on my spreadsheet. As of now, I have the same function copied into three separate functions.
With the exception of the below 3 lines, the rest of the VBA code is identical across all three functions:
ActiveWorkbook.Worksheets(
vs.
ActiveWorkbook.Worksheets(
vs.
ActiveWorkbook.Worksheets(
Now, if my range changes from, e.g., "A2:D13" to "A2:F20", I would have to make that change six (6) times vs. only twice if I had a singular function.
My question: If possible, can the VBA code be merged and then using, e.g., an IF statement (based on the caption of either "Sort by Column B" or "Sort by Column C" or "Sort by Column D")? If so, what would be the modified VBA code look like given that I still have three distinct command buttons being linked to one of the three VBA functions?
Please find attached sample XLS for additional details.
Thank you,
EEH
I need some help with streamlining (i.e., potentially merging three functions into one) some VBA code.
Background: I have three command buttons (i.e., "Sort By Column B", ""Sort By Column C", "Sort By Column D") on my spreadsheet. As of now, I have the same function copied into three separate functions.
With the exception of the below 3 lines, the rest of the VBA code is identical across all three functions:
ActiveWorkbook.Worksheets(
vs.
ActiveWorkbook.Worksheets(
vs.
ActiveWorkbook.Worksheets(
Now, if my range changes from, e.g., "A2:D13" to "A2:F20", I would have to make that change six (6) times vs. only twice if I had a singular function.
My question: If possible, can the VBA code be merged and then using, e.g., an IF statement (based on the caption of either "Sort by Column B" or "Sort by Column C" or "Sort by Column D")? If so, what would be the modified VBA code look like given that I still have three distinct command buttons being linked to one of the three VBA functions?
Please find attached sample XLS for additional details.
Thank you,
EEH
Private Sub Sort_By_B_Click()
'First Select the Range which you want to sort
Range("A2:D13").Select
'Now clear the Sort fields before sorting. This is important otherwise sorting will not take place
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
'Select the Range on which you want to perform the Sort.
'If it includes the header row as well then put .Header=xlYes, else xlNo.
.SetRange Range("A2:D13")
.Header = xlYes
'It is better to put MatchCase as False. In case you want Sorting as Case senstavie then you can make it "True"
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Private Sub Sort_By_C_Click()
'First Select the Range which you want to sort
Range("A2:D13").Select
'Now clear the Sort fields before sorting. This is important otherwise sorting will not take place
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
'Select the Range on which you want to perform the Sort.
'If it includes the header row as well then put .Header=xlYes, else xlNo.
.SetRange Range("A2:D13")
.Header = xlYes
'It is better to put MatchCase as False. In case you want Sorting as Case senstavie then you can make it "True"
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Private Sub Sort_By_D_Click()
'First Select the Range which you want to sort
Range("A2:D13").Select
'Now clear the Sort fields before sorting. This is important otherwise sorting will not take place
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D2"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
'Select the Range on which you want to perform the Sort.
'If it includes the header row as well then put .Header=xlYes, else xlNo.
.SetRange Range("A2:D13")
.Header = xlYes
'It is better to put MatchCase as False. In case you want Sorting as Case senstavie then you can make it "True"
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Last Comment
However, to answer your question whether its possible to combine into one routine, the simple answer is yes it is possible.
There is a an option but I can't recall the syntax to collect the text from the button when it is clicked so that it can then be used as a variable in the routine.
As for the changing range, it is not good practice to define a range within a routine for this very reason. It is good practice and often possible to avoid use of Select statement.
There is a an option but I can't recall the syntax to collect the text from the button when it is clicked so that it can then be used as a variable in the routine.
As for the changing range, it is not good practice to define a range within a routine for this very reason. It is good practice and often possible to avoid use of Select statement.
ASKER
Rob - thank you for the response. I am aware of the filter options.
In this particular case, I would prefer to stick with the VBA methodology though.
Again, thanks.
EEH
In this particular case, I would prefer to stick with the VBA methodology though.
Again, thanks.
EEH
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
There is an option to collect the Text from a button and use it as a variable in the routine but I don't recall the syntax.
For the Changing Range dilemma, you can create a Dynamic Named Range; the routine can then refer to that Named Range rather than a specific fixed range.
The following formula will create a dynamic named range:
=OFFSET(Data!$A$1,0,0,COUN
The syntax of Offset is:
=OFFSET(Reference, OffsetRows, OffsetColumns, Height, Width)
Reference - a known point from which you want to start
OffsetRows - a number of rows from the start point reference at which the range will start
OffsetColumns - a number of columns from the start point reference at which the range will start
Height - the number of rows to include in the Range
Width - the number of columns to include in the Range
So the above formula will set A1 on Data sheet as the reference point. It will offset by 0 rows and 0 columns (ie stay at A1) and will create a range with a height that is the number of populated cells in column A of Data sheet and width that is the number of populated cells in row 1 of Data sheet.
Likewise, if you convert the data range to a Table you can use the Table name rather than the range.
For the Changing Range dilemma, you can create a Dynamic Named Range; the routine can then refer to that Named Range rather than a specific fixed range.
The following formula will create a dynamic named range:
=OFFSET(Data!$A$1,0,0,COUN
The syntax of Offset is:
=OFFSET(Reference, OffsetRows, OffsetColumns, Height, Width)
Reference - a known point from which you want to start
OffsetRows - a number of rows from the start point reference at which the range will start
OffsetColumns - a number of columns from the start point reference at which the range will start
Height - the number of rows to include in the Range
Width - the number of columns to include in the Range
So the above formula will set A1 on Data sheet as the reference point. It will offset by 0 rows and 0 columns (ie stay at A1) and will create a range with a height that is the number of populated cells in column A of Data sheet and width that is the number of populated cells in row 1 of Data sheet.
Likewise, if you convert the data range to a Table you can use the Table name rather than the range.
ASKER
Ejgil:
Thank you for chiming in... the proposed solution is definitely going in the right direction.
Instead of having to enter either "B2, "C2", or "D2", is there a way to have the form pre-populated with, e.g., "B" or "C" or "D"? Upon selecting either one of the pre-populated values then, the columns would be sorted accordingly.
Thank you in advance.
EEH
Thank you for chiming in... the proposed solution is definitely going in the right direction.
Instead of having to enter either "B2, "C2", or "D2", is there a way to have the form pre-populated with, e.g., "B" or "C" or "D"? Upon selecting either one of the pre-populated values then, the columns would be sorted accordingly.
Thank you in advance.
EEH
ASKER
Quick follow up... ideally, instead of just showing "B" or "C" or "D", is there a way to show a more meaningful identifier.
Let's say, that column B shows dates by "Opened Account", and column C shows dates by "Closed Accounts", and finally column D includes dates by "Last Reported", I would prefer to rather select values from the descriptions vs. (B2, or C2, or D2).
Is that possible, too?
EEH
Let's say, that column B shows dates by "Opened Account", and column C shows dates by "Closed Accounts", and finally column D includes dates by "Last Reported", I would prefer to rather select values from the descriptions vs. (B2, or C2, or D2).
Is that possible, too?
EEH
You don't have to enter B, C or D, select a cell in the column to sort on.
ASKER
Copy - understood.
Just to make sure, is there any way a pop-up form could include those 3 descriptive values though?
EEH
Just to make sure, is there any way a pop-up form could include those 3 descriptive values though?
EEH
If the range always start in A1, it is possible to find the headers and display them.
But the solution was intended to be flexible, so it will sort any range anywhere on the sheet.
The range to sort is defined by the region the selected cell is in.
But the solution was intended to be flexible, so it will sort any range anywhere on the sheet.
The range to sort is defined by the region the selected cell is in.
ASKER
Roger - thank you for the additional feedback. I appreciate it.
ASKER
Thank you for the assistance. I very much appreciate it.
You may also use a UserForm with a ListBox which would contain the column headers for you to select a header you want data to sort on.
The solution is not limited to just three headers. You may add more headers and data below them and the same UserForm will identify them and will show them in the ListBox for you to select one of them. In that case, you may need to adjust the size of ListBox and adjust other controls as well.
Please refer to the attached and click on the button called "Sort Data" and follow the self explanatory UserForm.
Sorting.xlsm
The solution is not limited to just three headers. You may add more headers and data below them and the same UserForm will identify them and will show them in the ListBox for you to select one of them. In that case, you may need to adjust the size of ListBox and adjust other controls as well.
Please refer to the attached and click on the button called "Sort Data" and follow the self explanatory UserForm.
Sorting.xlsm
ASKER
Subodh:
Yet again, you provided an excellent solution to this question. I like your approach.
I tried to follow the logic of the VBA. Would it be too much to ask adding a few comment lines as to what some of the lines do? That would help me further customizing my solution.
Also, I clicked on the Listbox (and searched for string), e.g., "Sort by B". I couldn't located as to where in the userform the list box is populated (in case I wanted to add a 4th column and/or change the naming convention. Where do I find the code/property that drive the list box?
Thank you,
EEH
Yet again, you provided an excellent solution to this question. I like your approach.
I tried to follow the logic of the VBA. Would it be too much to ask adding a few comment lines as to what some of the lines do? That would help me further customizing my solution.
Also, I clicked on the Listbox (and searched for string), e.g., "Sort by B". I couldn't located as to where in the userform the list box is populated (in case I wanted to add a 4th column and/or change the naming convention. Where do I find the code/property that drive the list box?
Thank you,
EEH
You're welcome!
Thanks for the feedback.
The solution I offered is dynamic enough. The ListBox is populated when the UserForm initializes and you can find that code in a sub routine called "UserForm_Initialize" on UserForm Module.
You can rename the column headers or add more column headers and the ListBox will pick those column headers automatically considering the column headers are in row1 and start from column B.
Thanks for the feedback.
The solution I offered is dynamic enough. The ListBox is populated when the UserForm initializes and you can find that code in a sub routine called "UserForm_Initialize" on UserForm Module.
You can rename the column headers or add more column headers and the ListBox will pick those column headers automatically considering the column headers are in row1 and start from column B.
ASKER
Oh, that certainly makes the solution even better!
In the event I had to add a column, e.g., between current column A and B -- and thus making the 3 sort columns C, D, E, where do I modify the VBA to accommodate for this adjustment?
Thank you,
EEH
In the event I had to add a column, e.g., between current column A and B -- and thus making the 3 sort columns C, D, E, where do I modify the VBA to accommodate for this adjustment?
Thank you,
EEH
Is it possible for you to upload a sample file so that it would be easy to visualize the data layout?
ASKER
Thank you, Subodh. Please see attached the modified version. Reference the info in the call out for additional details.
EEH
Sorting-Modified.xlsm
EEH
Sorting-Modified.xlsm
You need to tweak the following code. The counter variable i at line#8 is changed from 2 to 3 in order to pick the headers starting from column C.
Private Sub UserForm_Initialize()
Dim i As Long
Set sws = Sheets("Sheet1")
lr = sws.Cells(Rows.Count, 1).End(xlUp).Row
lc = sws.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 3 To lc
Me.lstHeaders.AddItem sws.Cells(1, i)
Next i
End Sub
ASKER
Excellent -- thank you for chiming in here. Question had already been closed and points were previously awarded.
Your proposed solution definitely is an excellent solution. Again, thanks!
EEH
Your proposed solution definitely is an excellent solution. Again, thanks!
EEH
No problem. Glad I could help.
Thanks for the feedback. I appreciate it.
You can still mark my solution as helpful I guess. :)
Thanks for the feedback. I appreciate it.
You can still mark my solution as helpful I guess. :)
ASKER
Subodh:
I have integrated your solution into my actual file. As always, there's something slightly different that requires me to ask a follow-up question.
Below are two follow-ups:
1. In my actual XLS, I have columns ranging from A:S (vs. sample file only inc. A:E). Last version allowed me to ensure I populate the user form's listbox starting with column C. In my actual solution, I also want to start with C but only include ** 3 columns ** (C, D, E) vs. showing columns C:S. Thus, my question: How should I modify the code to include a particular range (C:E) for columns included in the listbox?
2. Just like in the sample file, my actual data has row #1 as the "header rows" and the data starts in row #2. Upon clicking in a column all data for rows #2 through 13 (again, in sample file) are sorted correctly. Similarly, in my actual file, I have the same setup (well, my data is in rows 2:37 vs. 2:13. However, now when sorting the data, the header row #1 is also sorted. My question: Why is my actual file also sorting the header row #1 when the sample file did not include the header row? What am I missing/overlooking?
EEH
I have integrated your solution into my actual file. As always, there's something slightly different that requires me to ask a follow-up question.
Below are two follow-ups:
1. In my actual XLS, I have columns ranging from A:S (vs. sample file only inc. A:E). Last version allowed me to ensure I populate the user form's listbox starting with column C. In my actual solution, I also want to start with C but only include ** 3 columns ** (C, D, E) vs. showing columns C:S. Thus, my question: How should I modify the code to include a particular range (C:E) for columns included in the listbox?
2. Just like in the sample file, my actual data has row #1 as the "header rows" and the data starts in row #2. Upon clicking in a column all data for rows #2 through 13 (again, in sample file) are sorted correctly. Similarly, in my actual file, I have the same setup (well, my data is in rows 2:37 vs. 2:13. However, now when sorting the data, the header row #1 is also sorted. My question: Why is my actual file also sorting the header row #1 when the sample file did not include the header row? What am I missing/overlooking?
EEH
For the first query, change the counter values at line#8 in the above code as below...
I am not sure about the second query. Do you have values in column A as the last row is being calculated based on column A?
The following lines sort the data...
If the Rng is not correct, you can figure out the reason.
For i = 3 To 5
I am not sure about the second query. Do you have values in column A as the last row is being calculated based on column A?
The following lines sort the data...
Set Rng = sws.Range("A2", sws.Cells(lr, lc))
sws.Sort.SortFields.Clear
Rng.Sort key1:=ColRng.Offset(1), order1:=xlAscending, Header:=xlNo
If the Rng is not correct, you can figure out the reason.
ASKER
Subodh:
Yes, column A was blank. That was intended.
Changing the For Loop, however, causes another set of problems.
I have attached a modified sample XLS that further explains the issues.
Thank you,
EEH
Yes, column A was blank. That was intended.
Changing the For Loop, however, causes another set of problems.
I have attached a modified sample XLS that further explains the issues.
Thank you,
EEH
Please test the attached and let me know if that works as desired.
Sorting--via-Userform-----Modified-.xlsm
Sorting--via-Userform-----Modified-.xlsm
ASKER
Perfect!! Thousand thanks for the prompt response/solution. :)
Cheers,
EEH
Cheers,
EEH
ASKER
Sorry, final question...
My actual data file has additional information (including merged cells) below the framework (B1:H13).
Apparently, the sorting function currently sorts the entire column. Instead, however, I need the sort function to include only rows 2 through 13 (in sample file).
How can I modify the VBA to include a hard-coded row reference (i.e., last row to be included in the sort)?
Sorry but the actual data file is slightly different that the sample file.
My actual data file has additional information (including merged cells) below the framework (B1:H13).
Apparently, the sorting function currently sorts the entire column. Instead, however, I need the sort function to include only rows 2 through 13 (in sample file).
How can I modify the VBA to include a hard-coded row reference (i.e., last row to be included in the sort)?
Sorry but the actual data file is slightly different that the sample file.
There is a variable called Rng which holds the range the code applies the sort on. All you need is to set this Rng variable with hard coded range reference.
There are two such instances on the UserForm Module.
1)
2)
e.g. if you want to apply sort on the fixed range say B2:H13, the second instance should be written as...
There are two such instances on the UserForm Module.
1)
Set Rng = sws.Range("B2", sws.Cells(lr, lc))
2)
Set Rng = sws.Range("B2", sws.Cells(lr, lc))
e.g. if you want to apply sort on the fixed range say B2:H13, the second instance should be written as...
Set Rng = sws.Range("B2:H13")
ASKER
That did the trick! Thank you again for your awesome/dedicated assistance.
EEH
EEH
You're welcome again! :)
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
You can use one of a couple of features of Excel instead.
Table
If you convert your data to a Table you will automatically get Sort and Filter options on all columns. If the data range changes, new rows of data will be automatically included; new columns should automatically be included into the table setting depending on how they are created.
Downside is that the headers cannot be calculated fields or numbers, they have to be text.
Auto Filter
By applying an Auto Filter to the data you get the same Filter and Sort options as mentioned above. Additional rows of data should get included but columns won't be. Headers can be anything you want.
Both options will give a drop-down menu option in the header row of each column from which you can select Sorting (Ascending or Descending) and filter options.