Why won't Excel sort these cells from left to right?

Brian F · Nov 22, 2016

[TABLE="width: 560"]
<colgroup><col width="50"><col width="51"><col width="51"><col width="51"><col width="51"><col width="51"><col width="51"><col width="51"><col width="51"><col width="51"><col width="51"></colgroup><tbody>[TR]
[TD="class: xl70, width: 51, align: center"] [TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD="width: 87"]

ZEBncRainFst

[/TD]
[TD="width: 94"]

ZEBncSavanna

[/TD]
[TD="width: 92"]

MPHwvPippin

[/TD]
[TD="width: 27"]

c

[/TD]
[TD="width: 27"]

c

[/TD]
[TD="width: 27"]

a

[/TD]
[TD="width: 28"]

b

[/TD]
[TD="width: 87"]

MPHfcBbbbb

[/TD]
[TD="width: 27"]

a

[/TD]
[TD="width: 31"]

Zz

[/TD]
[TD="width: 31"]

Zz

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Windows 10, Excel 2010, the macro runs, it just doesn't sort. I've tried it many times in every way I can think of and it won't sort. What's going on?

Sub Sort_LeftToRight()

Range("A1:AV11").Select
ActiveWorkbook.Worksheets("ProjectVsTask").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("ProjectVsTask").Sort.SortFields.Add Key:=Range( _
"A1:A11"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("ProjectVsTask").Sort
.SetRange Range("A1:A11")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.Apply
End With
End Sub

Joe4 · Nov 22, 2016

By default, Excel does vertical and not horizontal sorting.
See here for horizontal sorting: https://www.extendoffice.com/documents/excel/897-excel-sort-columns.html

wwbwb · Nov 22, 2016

Try replacing the A11 with a K1

Norie · Nov 22, 2016

You've set the range to sort as A1:A11 which is a column of data, try using A1:AV11 in the sort code.

Jonmo1 · Nov 22, 2016

The RED needs to be the ENTIRE range.
The BLUE needs to be only the single ROW you want to be the sort key.

Rich (BB code):

Sub Sort_LeftToRight()

    ActiveWorkbook.Worksheets("ProjectVsTask").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("ProjectVsTask").Sort.SortFields.Add Key:=Range( _
        "A1:AV1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("ProjectVsTask").Sort
        .SetRange Range("A1:AV11")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .Apply
    End With
 End Sub

Brian F · Nov 22, 2016

I'm sorry everyone, in an attempt to simplify my code to present it here I showed the incorrect range. The actual sort range in my code is "AK22:AV22" (and it is correctly entered in the code) so, as you can see I'm sorting in one row (Row 22) horizontally between columns AK & AV. I understand the procedures to sort horizontally. If I made the values "a,z,b,w,m,x" it will sort to "a,b,m,t,x,z" i.e. it works but it won't work with the values I've shown. any ideas?

Jonmo1 · Nov 22, 2016

Are the cells in AK22:AV22 actually containing formulas ??

Brian F · Nov 22, 2016

Jonmo1 said:
Are the cells in AK22:AV22 actually containing formulas ??

The values are all constants. When I try to run the macro it hangs-up at ".Apply" with the run-time error 1004 "The sort reference is not valid. Make sure that it's within the data you want to sort, and the First Sort By box isn't the same or blank".

I tried changing the values. It will sort numbers, and a single letter but hangs up on anything more complicated.

Jonmo1 · Nov 22, 2016

Can you post the actual 'real' code ?

MARK858 · Nov 22, 2016

Obviously do as Jonmo1 has asked and post the real code you are using but I am bit puzzled by this as when I run the code below with your data I get...

Excel Workbook

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

22

a

b

c

MPHfcBbbbb

MPHwvPippin

ZEBncRainFst

ZEBncSavanna

Zz

ProjectVsTask

Code:

Sub sxf()
   
    ActiveWorkbook.Worksheets("ProjectVsTask").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("ProjectVsTask").Sort.SortFields.Add Key:=Worksheets("ProjectVsTask").Range( _
                                                                        "Ak22"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                                                                   xlSortNormal
    With ActiveWorkbook.Worksheets("ProjectVsTask").Sort
        .SetRange Range("Ak22:Au22")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .Apply
    End With
End Sub

Why won't Excel sort these cells from left to right?

Brian F

Active Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

wwbwb

Well-known Member

Norie

Well-known Member

Jonmo1

MrExcel MVP

Brian F

Active Member

Jonmo1

MrExcel MVP

Brian F

Active Member

Jonmo1

MrExcel MVP

MARK858

MrExcel MVP

Similar threads

Forum statistics

Share this page

Why won't Excel sort these cells from left to right?

Active Member

Excel Facts

MrExcel MVP, Junior Admin

Well-known Member

Well-known Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

MrExcel MVP

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