Re: Autosort, but keep Row 1 seperate

I have this code that you gave me, but I don't know how to insert it into my
coding. When I copy and paste it as is, I receive a syntax error. Could you
tell me from start to finish how you would write the coding?:


dim LastRow as long
dim RngToSort as range
with worksheets("sheet9999")
lastrow = .cells(.rows.count,"A").end(xlup).row
set rngtosort = .range("a3:s" & lastrow)
with rngtosort
Key1:=.columns(7), order1:=xlDescending, _
Key2:=.columns(11), order2:=xlDescending, _
Key3:=.columns(12), order3:=xlDescending, _
header:=xlno
end with
end with


"Dave Peterson" wrote:

It sounds like your headers are in row 1, you have the =average() formulas in
row 2 and the data in rows 3:xxxx.

You could sort rows 3 to xxxx and turn headers to off.

I like to do something like:

dim LastRow as long
dim RngToSort as range
with worksheets("sheet9999")
lastrow = .cells(.rows.count,"A").end(xlup).row
set rngtosort = .range("a3:s" & lastrow)
with rngtosort
Key1:=.columns(7), order1:=xlDescending, _
Key2:=.columns(11), order2:=xlDescending, _
Key3:=.columns(12), order3:=xlDescending, _
header:=xlno
end with
end with

===
PS. Another thing you may want to consider. Put your average formulas in Row
1. Put the headers in Row 2.

Then select A3 and window|Freeze panes.

Then the averages will always be visible above the headers. And the averages
look like they're not part of the data.

PPS. And you may want to consider using =subtotal(1,...) as your average
formulas. If you apply data|filter|autofilter, then this function will ignore
the hidden rows. Kind of neat if you're looking at only certain categories.

Orangepegs wrote:

That was very helpful, thank you. It solved the header issue. However, there
is one more issue at hand. The cell G2 is meant to average a2:f2. Whenever
the autosort kicks in, it leaves G2 as a blank cell with no more formula. Is
there a way to correct that?

Thanks,

Orangepegs

"Dave Peterson" wrote:

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


--

Dave Peterson

.