Automatic sorting

[Matti]

Hi it's me again,

I've got a table containing 12 columns. One of the columns data is a mixture of % and text. I need to sort this data in ascending order whereby the highest % is at the top and below that I’d like to see the text in ascending alphabetical order. When a new record is entered I need it to be automatically placed in the right ascending order. How do I go about it?

Many tks,

Matti.

 

[philR]

OK, here is one way:

First, record a macro to sort your data ascending by your column (lets imagine in your table, the titles are in row 1 and the data is in rows 2,3,... Also, lets assume the column you want to sort by is columb B)

This gets the percentages to the top (but the wrong way round) and the text as it should be. Next, use a piece of code like this:

myrow = 1
Do
myrow = myrow + 1
Loop Until IsNumeric(Range("B" & myrow)) = False

This will tell you where the numbers end. Now, just copy and paste your sort routine in again, but change the last row to myRow-1. (and sort to descending)

It will look something like this:

Sub mysort()
Range("a2:L100").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

myrow = 1
Do
myrow = myrow + 1
Loop Until IsNumeric(Range("B" & myrow)) = False

Range("A2:L" & myrow - 1).Select
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub
Hope this is some help
This message was edited by philR on 2002-05-09 07:26
This message was edited by philR on 2002-05-09 07:27
This message was edited by philR on 2002-05-09 07:37
This message was edited by philR on 2002-05-09 07:38