Sorting Data Code Issues

Big_Chew

New Member
Joined
Oct 15, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I swear this was working the other day but now for the life of me I cannot figure out what is wrong. Below is a snippet of my data set and the code I am trying to use.

1603201027353.png


VBA Code:
Private Sub CommandButton2_Click()
'Sorts Raw Data Button
'Sorts Raw Data on Equipment, Year and Period
    With ActiveSheet.Sort
'Clears sorting
    .SortFields.Clear
    .SortFields.Add Key = ("D7"), Order:=xlAscending
    .SortFields.Add Key = ("A7"), Order:=xlAscending
    .SortFields.Add Key = ("B7"), Order:=xlAscending
    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    .SetRange Range("A7:L" & lr)
    .Header = xlNo
    .Apply

End With


End Sub
 
Yes I am referring to column B. When I sort like one would typically do and record the macro, I get the below code. I tried to mash the code you provided with the DataOption portions of the Macro code. Really in the end of things I can sort the data normally, but I wanted the button for other co workers that are not as experienced with excel.
VBA Code:
Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveWorkbook.Worksheets("Raw Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Raw Data").Sort.SortFields.Add2 Key:=Range( _
        "D7:D705"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Raw Data").Sort.SortFields.Add2 Key:=Range( _
        "A7:A705"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Raw Data").Sort.SortFields.Add2 Key:=Range( _
        "B7:B705"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Raw Data").Sort
        .SetRange Range("A6:L705")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Cool. I wasn't aware of the "Sort Text as Numbers" option.

Try this:
VBA Code:
Private Sub CommandButton2_Click()
'Sorts Raw Data Button
'Sorts Raw Data on Equipment, Year and Period

    Dim lr As Long
    
    lr = Cells(Rows.Count, "A").End(xlUp).Row

    Range("A6:L" & lr).Sort _
        Key1:=Range("D6"), Order1:=xlAscending, _
        Key2:=Range("A6"), Order2:=xlAscending, _
        Key3:=Range("B6"), Order3:=xlAscending, _
            DataOption3:=xlSortTextAsNumbers, Header:=xlYes

End Sub
 
Upvote 0
Solution
Cool. I wasn't aware of the "Sort Text as Numbers" option.

Try this:
VBA Code:
Private Sub CommandButton2_Click()
'Sorts Raw Data Button
'Sorts Raw Data on Equipment, Year and Period

    Dim lr As Long
   
    lr = Cells(Rows.Count, "A").End(xlUp).Row

    Range("A6:L" & lr).Sort _
        Key1:=Range("D6"), Order1:=xlAscending, _
        Key2:=Range("A6"), Order2:=xlAscending, _
        Key3:=Range("B6"), Order3:=xlAscending, _
            DataOption3:=xlSortTextAsNumbers, Header:=xlYes

End Sub

Yes this works perfectly. Thanks you for the help.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,720
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top