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
 

Big_Chew

New Member
Joined
Oct 15, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Big_Chew

New Member
Joined
Oct 15, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,980
Messages
5,621,951
Members
415,869
Latest member
LWSkinner

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
Top