modify a macro to have startCol and endCol

[DeezNuts]

I currently have a macro that works for columns 1:5

Sub SortAreas()
    Dim i As Long, j As Long, startRow As Long, areaRows As Long, areaCols As Long, sortKeyCol As Long, sortAscending As Boolean
    Dim a, s As Long
    startRow = 6
    areaCols = 5
    areaRows = 3
    sortKeyCol = 4
    If MsgBox("Do you want to sort grades ascending or descending?" & vbCrLf & vbCrLf & "Click [Yes] for ascending" & vbCrLf & "Click [No] for descending", vbYesNo, "Sort by?") = vbYes Then
        sortAscending = True
    Else
        sortAscending = False
    End If
    
    Application.ScreenUpdating = False
    With ActiveSheet
        For i = startRow To .Cells(.Rows.Count, sortKeyCol).End(xlUp).Row Step areaRows + 1
            s = 0
            a = .Cells(i, 1).Resize(areaRows, areaCols).Value
            For j = i + areaRows + 1 To .Cells(.Rows.Count, sortKeyCol).End(xlUp).Row Step areaRows + 1
                If s = 0 Then
                    If sortAscending Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value < a(1, sortKeyCol) Then s = j
                    If Not sortAscending Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value > a(1, sortKeyCol) Then s = j
                Else
                    If sortAscending Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value < .Cells(s, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value Then s = j
                    If Not sortAscending Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value > .Cells(s, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value Then s = j
                End If
            Next
            If s > 0 Then
                .Cells(i, 1).Resize(areaRows, areaCols).Value = .Cells(s, 1).Resize(areaRows, areaCols).Value
                .Cells(s, 1).Resize(areaRows, areaCols).Value = a
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

I would like to know how would I make the script work with columns 7:11 with 10 being the sortKeyCol. Here is what I tried

Sub SortTest()
    Dim i As Long, j As Long, startCol As Long, endCol As Long, startRow As Long, areaRows As Long, startCol As Long, endCol As Long, areaCols As Long, sortKeyCol As Long, sortAscendingTest As Boolean
    Dim a, s As Long
    startCol = 7
    endCol = 11
    startRow = 6
    areaCols = 5
    areaRows = 3
    sortKeyCol = 10
    If MsgBox("Do you want to sort grades ascending or descending?" & vbCrLf & vbCrLf & "Click [Yes] for ascending" & vbCrLf & "Click [No] for descending", vbYesNo, "Sort by?") = vbYes Then
        sortAscendingTest = True
    Else
        sortAscendingTest = False
    End If
    
    Application.ScreenUpdating = False
    With ActiveSheet
        For i = startRow To .Cells(.Rows.Count, sortKeyCol).End(xlUp).Row Step areaRows + 1
            s = 0
            a = .Cells(i, 1).Resize(areaRows, areaCols).Value
            For j = i + areaRows + 1 To .Cells(.Rows.Count, sortKeyCol).End(xlUp).Row Step areaRows + 1
                If s = 0 Then
                    If sortAscendingTest Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value < a(1, sortKeyCol) Then s = j
                    If Not sortAscendingTest Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value > a(1, sortKeyCol) Then s = j
                Else
                    If sortAscendingTest Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value < .Cells(s, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value Then s = j
                    If Not sortAscendingTest Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value > .Cells(s, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value Then s = j
                End If
            Next
            If s > 0 Then
                .Cells(i, 1).Resize(areaRows, areaCols).Value = .Cells(s, 1).Resize(areaRows, areaCols).Value
                .Cells(s, 1).Resize(areaRows, areaCols).Value = a
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Not sure what I need to change in the code itself to prevent getting a run-time 9 error and make it work. Could someone lend a hand with this please.

 

[mole999]

Not sure

I think


a = .Cells(i, 1).Resize(areaRows, areaCols).Value

needs to become

a = .Cells(i, 7).Resize(areaRows, areaCols).Value

and all others
</pre>

 

[DeezNuts]

Thank you for trying mole but that didnt do it. Hopefully different time of day new eyes on this will help

 

[DeezNuts]

Here is a stripped down version https://www.dropbox.com/s/fek37xem5n3hb4j/Sample.xlsm?dl=0 to practice with if anyone could lend a hand

 

[DeezNuts]

Could someone who has a second to look at this let me know if this can be done simply by editing or does another script need to be wrote.