Automatic sorting

Matti

New Member
Joined
May 7, 2002
Messages
8
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top