Sort breaks when columns are move around

[livetohike]

I write lots of simple macros (see below) to sort my data various
ways, but because the sort keys are associated to specific Columns,
each time I move columns around, the sort breaks. Is there some way
to write the sort such that it will still work after I move around
some columns? It is a pain to have to back and manyally update all the
cell names. Maybe using the Header Row names?

Seems like Excel is so very good at updating dell references when you
insert, delete, and move things around that there is probably a better
way to do this.


Sub SortByStateDisctrictSchool()
Cells.Select
Selection.Sort Key1:=Range("G2"), Order1:=xlAscending,
Key2:=Range("H2") _
, Order2:=xlAscending, Key3:=Range("A2"), Order3:=xlAscending,
_
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
End Sub

Using Excel 2000
Thanks

 

[Dave Peterson]

How about an alternative?

Use invisible rectangles over the headers that allows you to sort your data
based on the column of the rectangle that you clicked.

If you want to try that, check out Debra Dalgleish's site:
http://contextures.com/xlSort02.html

============
If you're using xl2003+ (IIRC), you could apply data|filter|autofilter. There's
an option under the dropdown arrow to sort by this field.

============

But if the header names don't change, you could use them.

Option Explicit
Sub SortByStateDisctrictSchool2()
Dim KeyCol(1 To 3) As Long
Dim KeyStr(1 To 3) As String
Dim iCtr As Long
Dim FoundCell As Range

KeyStr(1) = "State"
KeyStr(2) = "District"
KeyStr(3) = "School"

With ActiveSheet
With .Rows(1)
For iCtr = LBound(KeyStr) To UBound(KeyStr)
Set FoundCell = .Cells.Find(What:=KeyStr(iCtr), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If FoundCell Is Nothing Then
'missing header!
MsgBox "Fix the headers and try again!" & vbLf &
KeyStr(iCtr)
Exit Sub
End If

KeyCol(iCtr) = FoundCell.Column
Next iCtr
End With

'data starts in A1
With .UsedRange
.Sort key1:=.Columns(KeyCol(1)), order1:=xlAscending, _
key2:=.Columns(KeyCol(2)), order2:=xlAscending, _
key3:=.Columns(KeyCol(3)), order3:=xlAscending, _
header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
End With

End Sub