Generic sort macro for varying lengths

Hi again Alf,

It was a weird bug, as I initially posted the forum as unsolved. I had to mark this "solved" post as solved, then unsolved again before it registered properly on the forums...

Anyway, good catch, as I do want it to count possible blank rows preceding the last row. Implementing your suggestions in the code, I have:

Sub testsort2()
'
' testsort2 Macro
'

'
Dim LastRow As Long
    With ActiveSheet.UsedRange
    LastRow = .Rows(.Rows.Count).Row
    End With

    Rows("3:" & LastRow).Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("A3"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A3:" & LastRow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Now when I run I get "Runtime error '1004': Method 'Range' of object '_Global' failed" and it takes me to line

.SetRange Range("A3:" & LastRow)

As I figured earlier, I must be using the variable LastRow improperly with Range. What would be the correct syntax for this line?

Thank you,

-S

That makes sense.

I'm seeing now that my selection line must be redundant, as before the sorting is called in order it clears the sort fields and then defines it.. This is causing a problem as I noticed the sort is now working, but only sorting column A, without the other columns following the data in col A that they should correspond to.

Again, this is me wigging it and missing a crucial element, but this is how I attempted to fix my range issue:

        .SetRange Range("A3:LastCol" & LastRow)

where I defined LastCol earlier with:

With ActiveSheet.UsedRange
LastCol = .Columns(.Columns.Count).Column
End With

I figure this code would work as I intended if LastCol was a letter versus a number, but I know from my other posts that excel can recognize a column by a numerical representation as well. I tried using Cell(LastRow, LastCol) in the range field, but again I'm missing a factor. I know that is telling Excel to look at that Cell for a 'property,' but I'm not sure how to ask it look there for the cell "address" and return that to put in the second half of my range... Can you help clear this up for me?

-S

Oh, I was using the CELL function as mentioned here, confusing myself, hah. I used your range example with Cells() function now as I already defined LastCol.

The macro now works as intended. Here is the final code for anyone else who may want to reference the working generic sort macro:

Sub GenericSort3ThruX()
'
' GenericSort3ThruX Macro
'

'
Dim LastRow As Long
    With ActiveSheet.UsedRange
    LastRow = .Rows(.Rows.Count).Row
    LastCol = .Columns(.Columns.Count).Column
    End With
    
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("A3"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range(Cells(3, 1), Cells(LastRow, LastCol))
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Again, this code ignores the first two rows, which I use as headers on my sheets. If you would want to use this code and start on a different row, you could simply change the range lines to where you please, those lines being

ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("A3"), _
.SetRange Range(Cells(3, 1), Cells(LastRow, LastCol))

Once again you walked me through one of my issues, Alf. Thank you for the help, I appreciate it =).

-S