How about:

Option Explicit
Sub testme()
Dim myRngToSort As Range
Dim myBigRng As Range
Dim myPiecesRng As Range
Dim myArea As Range
Dim wks As Worksheet
Dim TotalColsToSort As Long
Dim KeyCol1 As Long
Dim KeyCol2 As Long
Dim KeyCol3 As Long
Dim ColThatGetsRanked As Long

Set wks = Worksheets("sheet1")

With wks
TotalColsToSort = 12
KeyCol1 = 10 'column j
KeyCol2 = 11 'column k
KeyCol3 = 1 'column A
ColThatGetsRanked = .Range("n1").Column

Set myBigRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

Set myPiecesRng = Nothing
On Error Resume Next
Set myPiecesRng = myBigRng.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myPiecesRng Is Nothing Then
MsgBox "No constants in column A!"
Exit Sub
End If

For Each myArea In myPiecesRng.Areas
With myArea
'come down 2 rows to avoid the headings
Set myRngToSort _
= .Resize(.Rows.Count - 2, TotalColsToSort).Offset(2, 0)
myRngToSort.Sort _
key1:=.Columns(KeyCol1), order1:=xlDescending, _
key2:=.Columns(KeyCol2), order1:=xlDescending, _
key3:=.Columns(KeyCol3), order1:=xlDescending, _
header:=xlNo
With myRngToSort.Resize(, 1).Offset(0, ColThatGetsRanked - 1)
.Formula = "=row()+1-" & myRngToSort.Row
.Value = .Value
End With
End With
Next myArea
End With

End Sub

Saxman wrote:

Saxman wrote:

myRngToSort.Sort _
key1:=.Columns(KeyCol1), order1:=xlAscending, _
key2:=.Columns(KeyCol2), order1:=xlAscending, _
key3:=.Columns(KeyCol3), order1:=xlAscending, _
header:=xlNo

This sorts column 'J' ascending. Ideally I would like it descending. I
assume I can alter the code to descending in the above?

I've replaced the above with descending and column 'J' sorts fine.

Once 'J' has been sorted ascending would it be possible to rank them
1,2,3, etc. in column 'N'.

By this I meant the AutoFill function and FillSeries.

--

Dave Peterson