"The Sort reference is not valid"

[JimSnyder]

I have built a subroutine for doing multi column sorts. The problem is that the error on the subject line does not tell me where it is breaking. Can experts at sorts eyeball this and see if a problem pops out?

Option Explicit ' Must declare variables - clean programming technique


Public Sub MultiSort(ByRef currentSheet As Worksheet, _
    ByVal sortColOne As String, ByVal sortOneDirection As Integer, _
    ByVal sortColTwo As String, ByVal sortTwoDirection As Integer, _
    ByVal sortColThree As String, ByVal sortThreeDirection As Integer, _
    ByVal hasHeaders As Integer)


    Dim boxReturn As String
    Dim lastCol As Integer
    Dim LastRow As Long
    Dim usedRange As Range
    
    With currentSheet
        ' Get range of data
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set usedRange = .Range(.Cells(1, "A"), .Cells(lastCol & LastRow))
        
        ' Find how many sort keys were passed; check from left parameter to right parameter
        ' First parameter
        If Len(sortColOne) > 0 Then
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=.Range("" & sortColOne & ":" & sortColOne & ""), _
                SortOn:=xlSortOnValues, Order:=sortOneDirection, _
                DataOption:=xlSortNormal
                
            ' Second parameter
            If Len(sortColTwo) > 0 Then
                .Sort.SortFields.Add Key:=.Range("" & sortColTwo & ":" & sortColTwo & ""), _
                    SortOn:=xlSortOnValues, Order:=sortTwoDirection, _
                    DataOption:=xlSortNormal
                    
                ' Third parameter
                If Len(sortColThree) > 0 Then
                    .Sort.SortFields.Add Key:=.Range("" & sortColThree & ":" & sortColThree & ""), _
                        SortOn:=xlSortOnValues, Order:=sortThreeDirection, _
                        DataOption:=xlSortNormal
                End If
            End If
        Else
            boxReturn = MsgBox("First parameter is empty. Please enter first key information.", vbOK, "Empty Parameter")
        End If
        
        ' Do the sort
        With .Sort
            .SetRange usedRange
            .Header = hasHeaders
            .MatchCase = False
            .Orientation = xlSortRows
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

The error '1004' is generated at the ".Apply".

 

[JimSnyder]

The answer was simple. I was using the wrong orientation. Corrected code below:

Option Explicit ' Must declare variables - clean programming technique


Public Sub MultiSort(ByRef currentSheet As Worksheet, _
    ByVal sortColOne As String, ByVal sortOneDirection As Integer, _
    ByVal sortColTwo As String, ByVal sortTwoDirection As Integer, _
    ByVal sortColThree As String, ByVal sortThreeDirection As Integer, _
    ByVal hasHeaders As Integer)


    Dim boxReturn As String
    Dim lastCol As Integer
    Dim LastRow As Long
    Dim usedRange As Range
    
    With currentSheet
        ' Get range of data
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set usedRange = .Range(.Cells(1, "A"), .Cells(lastCol & LastRow))
        
        ' Find how many sort keys were passed; check from left parameter to right parameter
        ' First parameter
        If Len(sortColOne) > 0 Then
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=.Range("" & sortColOne & ":" & sortColOne & ""), _
                SortOn:=xlSortOnValues, Order:=sortOneDirection, _
                DataOption:=xlSortNormal
                
            ' Second parameter
            If Len(sortColTwo) > 0 Then
                .Sort.SortFields.Add Key:=.Range("" & sortColTwo & ":" & sortColTwo & ""), _
                    SortOn:=xlSortOnValues, Order:=sortTwoDirection, _
                    DataOption:=xlSortNormal
                    
                ' Third parameter
                If Len(sortColThree) > 0 Then
                    .Sort.SortFields.Add Key:=.Range("" & sortColThree & ":" & sortColThree & ""), _
                        SortOn:=xlSortOnValues, Order:=sortThreeDirection, _
                        DataOption:=xlSortNormal
                End If
            End If
        Else
            boxReturn = MsgBox("First parameter is empty. Please enter first key information.", vbOK, "Empty Parameter")
            Exit Sub
        End If
        
        ' Do the sort
        With .Sort
            .SetRange usedRange
            .Header = hasHeaders
            .MatchCase = False
[COLOR=#ff0000][B]            .Orientation = xlSortColumns[/B][/COLOR]
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub