Sort macro

[Shadkng]

Hi, I recorded the macro to sort a range starting at A3 to column D, down to last row. I want to sort ascending in column C and then D. I changed the key:=Range("C3:C100" _ in line 6 & 8 to key:=Range("C3" _, and it seemed to work. However, I dont know what to do with the this on line 11: .SetRange Range("A3:D100") to not limit at 100. Thanks

Sub SORT_TOT_FAB()
Application.ScreenUpdating = False
Range("A3:D3").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("TOT FAB").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("TOT FAB").Sort.SortFields.Add2 key:=Range("C3:C100" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("TOT FAB").Sort.SortFields.Add2 key:=Range("D3:D100" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("TOT FAB").Sort
        .SetRange Range("A3:D100")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("E3").Select
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

Do you have a header row in row 3?

 

[Shadkng]

Do you have a header row in row 3?

No header

 

[Fluff]

In that case, try

Sub SORT_TOT_FAB()
   Application.ScreenUpdating = False
   With ActiveWorkbook.Worksheets("TOT FAB").Sort
      .SortFields.Clear
      .SortFields.Add2 Key:=Range("C3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SortFields.Add2 Key:=Range("D3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   
      .SetRange Range("A3:D" & .Parent.Range("A" & Rows.Count).End(xlUp).Row)
      .Header = xlNo
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With
   Range("E3").Select
   Application.ScreenUpdating = True
End Sub

This assumes you have data in col A for every row.

 

[Shadkng]

In that case, try

Sub SORT_TOT_FAB()
   Application.ScreenUpdating = False
   With ActiveWorkbook.Worksheets("TOT FAB").Sort
      .SortFields.Clear
      .SortFields.Add2 Key:=Range("C3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SortFields.Add2 Key:=Range("D3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  
      .SetRange Range("A3:D" & .Parent.Range("A" & Rows.Count).End(xlUp).Row)
      .Header = xlNo
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With
   Range("E3").Select
   Application.ScreenUpdating = True
End Sub

This assumes you have data in col A for every row.

Perfect! Thanks

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Shadkng]

Perfect! Thanks

Hi Fluff,

The below code is sorting by first digit, but I prefer to sort by number. I guess the ranges need to be formatted by number...I need to 2 places after the decimal for column C, no places for column D. And the range needs to be to the last row as it is now. Thanks again.

Sub SORT_TOT_FAB()
   Application.ScreenUpdating = False
   With ActiveWorkbook.Worksheets("TOT FAB").Sort
      .SortFields.Clear
      .SortFields.Add2 key:=Range("C3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SortFields.Add2 key:=Range("D3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SetRange Range("A3:D" & .Parent.Range("A" & Rows.Count).End(xlUp).Row)
      .Header = xlNo
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With
   Range("E3").Select
   Application.ScreenUpdating = True
End Sub

 

[Fluff]

If col C & D contain text not numbers then you will need to convert them to numbers, simply changing the cell format won't make any difference.

 

[Shadkng]

If col C & D contain text not numbers then you will need to convert them to numbers, simply changing the cell format won't make any difference.

Right now it comes up as general. When I change the format to number and run the macro it seems to sort the way I want it.

 

[Fluff]

Glad it's sorted & thanks for the feedback.