Sorting Numbers

colinh · Mar 10, 2011

Hi All

I have a worksheet with numbers in rows 3-34 and from columns D -T, I need a macro to sort the rows 3 - 34 numbers only from low to high numbers.

So to sum up it will sort row 3 from lowest number to highist number then move on to row 4 do the same then row 5 etc

Thanks

Colin

VoG · Mar 10, 2011

Hi. Try recording a macro whilst doing this for a few rows then post the recorded code and somebody will clean it up for you.

Weaver · Mar 10, 2011

You'd probably end up with something like

Code:

Sub Macro1()
    For i = 3 To 34
        Cells(i, 4).Resize(1, 17).Sort Key1:=Cells(i, 4), Orientation:=xlLeftToRight
    Next i
End Sub

colinh · Mar 10, 2011

Range("D3:T3").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D3") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D3:T3")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("D4:T4").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D4") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D4:T4")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("D5:T5").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D5") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D5:T5")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

colinh · Mar 10, 2011

Hi

My cells are not the same size it throws up a error

Colin

Weaver · Mar 10, 2011

Colin

Did you try my code from post #3?

Are you saying you have merged cells in your sheet?

What was the error? At which part of the code did it fail (the yellow highlighting)

colinh · Mar 11, 2011

Hi

I do have merged cells but nt the ones that I need to sort, however they are on the same row. I get the below error

RUN TIME ERROR 1004
THIS OPERATION REQUIRES THE MERGED CELLS TO BE IDENTICALLY SIZED

This is the yellow when running the macro

Cells(i, 4).Sort Key1:=Cells(i, 4), Orientation:=xlLeftToRight

Col

Weaver · Mar 11, 2011

Sorry colin, but that line of code you have posted doesn't appear in the subroutine I posted. Have you made any edits?

You need the .resize() part of the range in there to limit the search to a specific set of cells, otherwise, what I think is happening is that it's trying to sort the whole row, or at least as much of it that has data.

Although to be fair, the sample sheet I tested it on didn't have any merged cells in it.

HTH

Update: I've tested it with one big merged cell in column U (immediately to the right of the data) and there was no problem. Removing the .rezize() part of the function call produces the error you described.

Was there any particular reason why you changed it?

colinh · Mar 12, 2011

Hi

Please see below recorded macro can it be cleaned up and made smaller

Colin

Sub sort_life_numbers()
'
' sort_life_numbers Macro
'
'
Range("D3:T3").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D3") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D3:T3")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D4:T4").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D4") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D4:T4")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D5:T5").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D5") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D5:T5")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D6:T6").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D6") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D6:T6")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D7:T7").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D7") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin

End With

Range("D8:T8").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D8") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D8:T8")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D9:T9").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D9") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D9:T9")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D10:T10").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D10") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D10:T10")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D11:T11").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D11") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D11:T11")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D12:T12").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D12") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D12:T12")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D13:T13").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D13") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D13:T13")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D14:T14").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D14") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D14:T14")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D15:T15").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D15") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D15:T15")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D16:T16").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D16") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D16:T16")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D17:T17").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D17") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D17:T17")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D18:T18").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D18") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D18:T18")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D22:T22").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D22") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D22:T22")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D23:T23").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D23") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D23:T23")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D24:T24").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D24") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D24:T24")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D25:T25").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D25") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D25:T25")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D26:T26").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D26") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D26:T26")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D27:T27").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D27") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D27:T27")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D28:T28").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D28") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D28:T28")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D29:T29").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D29") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D29:T29")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D30:T30").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D30") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D30:T30")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D31:T31").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D31") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D31:T31")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D32:T32").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D32") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D32:T32")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D33:T33").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D33") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D33:T33")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D34:T34").Select
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D34") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D34:T34")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select

'sheet 2
Range("D3:T3").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D3") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D3:T3")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D4:T4").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D4") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D4:T4")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D5:T5").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D5") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D5:T5")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D6:T6").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D6") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D6:T6")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D7:T7").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D7") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin

End With

Range("D8:T8").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D8") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D8:T8")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D9:T9").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D9") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D9:T9")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D10:T10").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D10") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D10:T10")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D11:T11").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D11") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D11:T11")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D12:T12").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D12") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D12:T12")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D13:T13").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D13") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D13:T13")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D14:T14").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D14") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D14:T14")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D15:T15").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D15") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D15:T15")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D16:T16").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D16") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D16:T16")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D17:T17").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D17") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D17:T17")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D18:T18").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D18") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D18:T18")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D22:T22").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D22") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D22:T22")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D23:T23").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D23") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D23:T23")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D24:T24").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D24") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D24:T24")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D25:T25").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D25") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D25:T25")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D26:T26").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D26") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D26:T26")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D27:T27").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D27") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D27:T27")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D28:T28").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D28") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D28:T28")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D29:T29").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D29") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D29:T29")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D30:T30").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D30") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D30:T30")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply

End With

Range("D31:T31").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D31") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D31:T31")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D32:T32").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D32") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D32:T32")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D33:T33").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D33") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D33:T33")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Range("D34:T34").Select
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort.SortFields.Add Key:=Range("D34") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Fuel Steering Etc").Sort
.SetRange Range("D34:T34")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Sheets("Suspension").Select
Range("A1").Select

End Sub

Weaver · Mar 15, 2011

Sorry, Colin, are you saying the code I supplied didn't work at all?

I think this would do what you're suggesting

Code:

for i = 3 to 34
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Suspension").Sort.SortFields.Add Key:=Range("D" & i) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Suspension").Sort
.SetRange Range("D" & i & ":T" & i)
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
next i

Sorting Numbers

colinh

Board Regular

Excel Facts

VoG

Legend

Weaver

Well-known Member

colinh

Board Regular

colinh

Board Regular

Weaver

Well-known Member

colinh

Board Regular

Weaver

Well-known Member

colinh

Board Regular

Weaver

Well-known Member

Similar threads

Forum statistics

Share this page

Sorting Numbers

Board Regular

Excel Facts

Legend

Well-known Member

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock