Eric,

I am not sure what you are trying to do, but you cannot have a negative size for a range object.

Set myrange = myrange.Resize(lrow - myrange.Row + 1, -6)

But you can have a negative offset from a range object.... Perhaps:

Set myrange = myrange.Offset(0,-6).Resize(lrow - myrange.Row + 1,1)

For your second question, you can sort in a specific order if you first create a list in the order
that you desire, then use Tools / Options... Custom List to create a list. When you sort, you need
to pick that list from the "Sort Order" box. Record a macro when you do it to get the syntax and
list number.

HTH,
Bernie
MS Excel MVP


"Eric" wrote in message
...
If I sort from Column H to column A by putting a negative infront of the
letter it isn't working.

Dim sheet1 As Worksheet
Dim myrange As Range
Dim lrow As Long
Set sheet1 = ActiveSheet
Set myrange = sheet1.Range("h11")
lrow = sheet1.Cells(sheet1.Rows.count, myrange.Column).End(xlUp).Row

Set myrange = myrange.Resize(lrow - myrange.Row + 1, -6)<~~~~This is where I
am talking about. If I go from A to H everything is fine.

myrange.sort key1:=Range("h12"), Order1:=xlAscending, _
Key2:=Range("g12"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlSortNormal

Also, how do I get this to sort by Placing a specific contract number on top.

ie:
123
354
456

Now I want it to look like this
456
123
354