Excel VBA - Sort left to Right (But not handling header correctly)

Hi,

This is a follow-on question from one I raised earlier (which was resolved).

See attached.
Note that if you double-click on column "A" this will result in a sorting of the numbers in ascending sequence.  (Left to Right)

However, it ALSO sorts the header which I do not want to happen.

The VBA would appear to indicate that a header is present - so WHY is the header included in the sort.

Thanks!
SortLeftToRight.xlsm
Patrick O'DeaAsked:
Who is Participating?
 
Robberbaron (robr)Commented:
try this. it resizes the sortzone, and also removes hardcoded extents.

Private Sub Worksheet_BeforedoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim sortzone As Range, thisSheet As Worksheet
    If Target.Column = 1 And Target.Row > 1 Then
        Set thisSheet = ActiveWorkbook.ActiveSheet
        Set sortzone = thisSheet.Range("A1").CurrentRegion.Offset(0, 1)
        Set sortzone = sortzone.Resize(, sortzone.Columns.Count - 1)
        
        thisSheet.Sort.SortFields.Clear
        thisSheet.Sort.SortFields.Add Key:=Target.Offset(0, 1).Resize(, sortzone.Columns.Count), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With thisSheet.Sort
            .SetRange sortzone
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlLeftToRight
            .SortMethod = xlPinYin
            .Apply
        End With
        Cancel = True
    End If
    Range("A1").Select
End Sub

Open in new window

0
 
CompProbSolvCommented:
I noticed that when one tries to do the sort manually in Excel, if you select left-to-right then the box for "My Data has Headers" is greyed out.  I would assume that the two are mutually exclusive.

I would leave the headers out of the selection.
0
 
Patrick O'DeaAuthor Commented:
One word only!

Perfect!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.