Sorting Backwards

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eric - 24 Sep 2007 13:12 GMT

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

Reply to this Message

Bernie Deitrick - 24 Sep 2007 14:26 GMT

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

> If I sort from Column H to column A by putting a negative infront of the
> letter it isn't working.
[quoted text clipped - 25 lines]
> 123
> 354

Reply to this Message

Eric - 24 Sep 2007 19:20 GMT

Bernie,

What you told me to do didn't work. But maybe I can explain it better this
time.

Ex:

Gmm Gse date Contr#
2.567 2.987 9/12 123
2.777 2.991 9/13 345
2.568 2.988 9/14 123

What I want to do is be able to sort this information and graph it. The way
it is in the Ex. I can graph all properties in the order that it appears.
with the last test being posted on the last line. Now I want to graph by
contract placing (whichever Contract # I want) First in order to graph. I
want it to look like this

EX:

or like this

Gmm Gse date Contr#
2.777 2.991 9/13 345
2.567 2.987 9/12 123
2.568 2.988 9/14 123

Using the following macro I can make it work if the Contr # and Date are on
the Left of the sheet but not on the right. The other issue is when I use
this macro the contract #'s list in conilogical order not by whichever one I
want first.

Sub sortbydate()

' **********This is to sort the test data base according to Date when date
is on Left not right**************

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

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

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

End Sub

I hope this helps out.......

> Eric,
>
[quoted text clipped - 44 lines]
> > 123
> > 354

Reply to this Message