Excel dynamic sorting only does 167 rows of 25,000

[JimSnyder]

Obviously, I am doing something not quite right. If anyone can see what I am doing wrong, I would be ecstatic:

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

hasHeaders is being set in the call to xlYes.

 

[RickXL]

Hi,

I have not tested this but this line does not look right:

Set usedRange = .Range(.Cells(firstRow, "A"), .Cells(lastCol & lastRow))

This would be better:

Set usedRange = .Range(.Cells(firstRow, "A"), .Cells(lastRow, lastCol))

 

[JimSnyder]

RickXL - Much appreciated! That was it. Visual dyslexia...

 

[JimSnyder]

Actually, that was an error, but did not resolve the problem. The row last sorted change to 138. I also tried this:

DataOption:=xlSortTextAsNumbers

There was no other change.

 

[RickXL]

What number is lastRow set to?