troubleshooting Question

Streamline VBA code (sorting by 3 different columns)

Avatar of ExpExchHelp
ExpExchHelpFlag for United States of America asked on
Microsoft OfficeMicrosoft ExcelVBA
31 Comments1 Solution101 ViewsLast Modified:
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
Sorting.xlsm
ASKER CERTIFIED SOLUTION
Ejgil Hedegaard

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 31 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 31 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros