Re: Autosort, but keep Row 1 seperate

Tech-Archive recommends: Fix windows errors by optimizing your registry

I'm not sure what you're doing with "Key1:=Range("G2,k2,l2")", but if you really
meant to sort by 3 key fields, you'll want to use Key1, key2, key3.

And one of the options when you sort is to allow excel to guess at the header
row. Since you know your data has headers, just tell excel that in your code:

Columns("A:S").Sort _
Key1:=Range("G2"), order1:=xlDescending, _
Key2:=Range("k2"), order2:=xlDescending, _
Key3:=Range("l2"), order3:=xlDescending, _
header:=xlyes



Orangepegs wrote:

I've figured out how to autosort my columns, but there are two problems: I
can't seem to do it without messing up my frozen window pane in Row 1 (this
describes the information in the spread***), and I want an "average"
formula to populate one of the cells I use in the autosort formula (It
disappears every time the autosort function kicks in).

Anyhow, here's the formula I came up with. The main problem is with the
first row being included in the sort. I thought I had it covered, but
obviously it doesn't work.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("S2")) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
Columns("A:S").Sort Key1:=Range("G2,k2,l2"), order1:=xlDescending
Range("A2:S2").Insert Shift:=xlDown
Application.EnableEvents = True
End Sub

Any and all help is much appreciated!

--

Dave Peterson
.

Quantcast