Dave,

Thanks for the good point.... that's what I get for writing code without testing.

I shouldn't have nested the with structures - using a range object like this should reduce needing
to count columns, though.

Sub TryNow2()
Dim myR As Range
With ActiveWorkbook.Worksheets("Saturday")
Set myR = .Range(.Range("B7"), .Range("J7").End(xlDown))
myR.Sort Key1:=.Range("E8"), Order1:=xlAscending, _
Key2:=.Range("H8"), Order2:=xlDescending, _
Header:=xlGuess
myR.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd
End With
End Sub

Bernie
MS Excel MVP


"Dave Peterson" wrote in message
...
Just something to be watchful for...

With .Range(.Range("B7"), .Range("J7").End(xlDown))
.Sort Key1:=.Range("E8"), Order1:=xlAscending, _
Key2:=.Range("H8"), Order2:=xlDescending, _

Since you're within the B7:J### "with/end with" structure, the .range("E8") is
offset from that range (B7 is what's important). So in your sample code, you're
sorting by column F and I (when you look at the column headers).

I like this syntax:

Sub TryNow()
With ActiveWorkbook.Worksheets("Saturday")
With .Range("B7", .Range("J7").End(xlDown))
.Sort Key1:=.columns(4), Order1:=xlAscending, _
Key2:=.columns(7), Order2:=xlDescending, _
Header:=xlGuess
.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd
End With
End With
End Sub

inside this with statement
With .Range("B7", .Range("J7").End(xlDown))
.Sort Key1:=.columns(4), Order1:=xlAscending, _
Key2:=.columns(7), Order2:=xlDescending, _

.columns(1) would be column B
.columns(2) would be column C
...
.columns(7) would be column H

(I didn't see what columns the OP mentioned before--so I guessed with my code.)


=====
And yes, I changed this:
With .Range(.Range("B7"), .Range("J7").End(xlDown))
to
With .Range("B7", .Range("J7").End(xlDown))

Just because <vbg.



Bernie Deitrick wrote:

Greg,

This should work, assuming that you put the formulas into column J:

Sub TryNow()
With ActiveWorkbook.Worksheets("Saturday")
With .Range(.Range("B7"), .Range("J7").End(xlDown))
.Sort Key1:=.Range("E8"), Order1:=xlAscending, _
Key2:=.Range("H8"), Order2:=xlDescending, _
Header:=xlGuess
.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd
End With
End With
End Sub

Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to
the
column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have
headers
and xlNo if you don't.

HTH,
Bernie
MS Excel MVP

"Greg" wrote in message
...
Thank You. i used that on my sample and works perfect! i'm applying this to
my actual worksheet but i have some troubles setting this up correctly.

1. The sort area is from B7:J106 (so there is more that just the two Columns
to sort but those two Columns i mentioned determine the sort order. i do not
want column A sorted.
2. How would i code this to sort and filter? below is what i have so far
assigned to a button that sorts the way i want but not sending the lower
value duplicates to the bottom of list.
Sub SortSaturday()
'
' Sort for Saturday
'

'
Range("J7:J106").Select
ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear
ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add
Key:=Range("J7"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Saturday").sort
.SetRange Range("B7:J106")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Once again,
MANY THANKS IN ADVANCE!

"Bernie Deitrick" wrote:

Greg,

In the next column - for our example, we will use column C, so use cell C2 - use the formula

=SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2))

Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down.

Finally, sort by column C ascending, then column B descending.

HTH,
Bernie
MS Excel MVP


"Greg" wrote in message
...
I have a button that sorts multiple columns based on one row. in another row
there are duplicate values ( Peoples names). i would like to keep all records
but would like to send the duplicates to the bottom. sorry I'm not sure of
the proper way to ask this. so here is a basic example.

John Doe; 678
Jane Doe; 654
John Doe: 721
Jim Smith; 672
Jim Smith; 673
John Doe; 688
Sam Andrews; 700

I would like the above to sort like this
John Doe; 721
Sam Andrew; 700
Jim Smith; 673
Jane Doe; 654
John Doe; 688
John Doe; 678
Jim Smith; 672


Sort the number column largest to smallest; keep any lower number duplicate
names sorted on the bottom.

Many Thanks in advance
Greg


.


--

Dave Peterson