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("Equifax Report").Sort.SortFields.Add Key:=Range("B2"), SortOn:=xlSortOnValues, _
vs.
ActiveWorkbook.Worksheets("Equifax Report").Sort.SortFields.Add Key:=Range("C2"), SortOn:=xlSortOnValues, _
vs.
ActiveWorkbook.Worksheets("Equifax Report").Sort.SortFields.Add Key:=Range("D2"), SortOn:=xlSortOnValues, _

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

Open in new window

Sorting.xlsm
ExpExchHelpAnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Why use VBA to Sort the data??

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.
0
Rob HensonFinance AnalystCommented:
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.
0
ExpExchHelpAnalystAuthor Commented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ejgil HedegaardCommented:
Try attached.
Use button "Sort by selection" and select a cell in the column you want to sort on.
Sorting.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
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,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

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.
0
ExpExchHelpAnalystAuthor Commented:
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
0
ExpExchHelpAnalystAuthor Commented:
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
0
Ejgil HedegaardCommented:
You don't have to enter B, C or D, select a cell in the column to sort on.
0
ExpExchHelpAnalystAuthor Commented:
Copy - understood.  

Just to make sure, is there any way a pop-up form could include those 3 descriptive values though?

EEH
0
Ejgil HedegaardCommented:
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.
0
ExpExchHelpAnalystAuthor Commented:
Roger - thank you for the additional feedback.  I appreciate it.
0
ExpExchHelpAnalystAuthor Commented:
Thank you for the assistance.  I very much appreciate it.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
0
ExpExchHelpAnalystAuthor Commented:
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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0
ExpExchHelpAnalystAuthor Commented:
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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Is it possible for you to upload a sample file so that it would be easy to visualize the data layout?
0
ExpExchHelpAnalystAuthor Commented:
Thank you, Subodh.   Please see attached the modified version.   Reference the info in the call out for additional details.

EEH
Sorting-Modified.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

Open in new window

Sorting-Modified.xlsm
0
ExpExchHelpAnalystAuthor Commented:
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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
No problem. Glad I could help.
Thanks for the feedback. I appreciate it.
You can still mark my solution as helpful I guess. :)
0
ExpExchHelpAnalystAuthor Commented:
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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
For the first query, change the counter values at line#8 in the above code as below...
For i = 3 To 5

Open in new window


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

Open in new window

In the above code, place a line MsgBox Rng.Address, that will give you an idea about which range is being sorted by the code.
If the Rng is not correct, you can figure out the reason.
0
ExpExchHelpAnalystAuthor Commented:
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
0
ExpExchHelpAnalystAuthor Commented:
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please test the attached and let me know if that works as desired.
Sorting--via-Userform-----Modified-.xlsm
0
ExpExchHelpAnalystAuthor Commented:
Perfect!!    Thousand thanks for the prompt response/solution.   :)

Cheers,
EEH
0
ExpExchHelpAnalystAuthor Commented:
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.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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)
Set Rng = sws.Range("B2", sws.Cells(lr, lc))

Open in new window


2)
Set Rng = sws.Range("B2", sws.Cells(lr, lc))

Open in new window


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")

Open in new window

Please note that the range B2:H13 should not include the header row.
0
ExpExchHelpAnalystAuthor Commented:
That did the trick!   Thank you again for your awesome/dedicated assistance.

EEH
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome again! :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.