I declared a variable named keycol--I meant to use that in the sort routine.
But you can specify up to 3 keys in your sort. I used keycol1, keycol2, and
keycol3. I wasn't sure when/what you sorted.

But this line:

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

is the line that does all the real work for each group of data.

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

Set wks = Worksheets("sheet1")

With wks

TotalColsToSort = 12

KeyCol1 = 10 'column j
KeyCol2 = 11 'column k
KeyCol3 = 1 'column A

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:=xlAscending, _
key2:=.Columns(KeyCol2), order1:=xlAscending, _
key3:=.Columns(KeyCol3), order1:=xlAscending, _
header:=xlNo
End With
Next myArea
End With

End Sub

Saxman wrote:

Saxman wrote:

There are no formulas within the worksheet, just numbers and text.

The blank cells are completely empty.

I ought to add that when I import the data into a worksheet, column 'L'
is already sorted. I then rank the data from column 'L', 1 to whatever
for simplicity. I then sort columns 'J' and 'K' and do similar.

I was hoping to make a start with the code you have provided and then to
create a macro with the other bits to do the numbering added on later.

I need to know how to sort other columns with the code provided.

Thanks again for your help.

--

Dave Peterson