sorting numbers into ascending order

**stevy123** · 04-25-2007, 02:02 PM

Hi All

I am currently writing a visual basic program which will input 100 random numbers into excell. Is there any function that will automatically arange them into ascending order?

Somethign like =SORT(A1:a100)

Anyones input is greatly appriciated.

Regards
Stephen

**davesexcel** · 04-25-2007, 02:12 PM

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Column <> 1 Then Exit Sub
   Worksheets("Sheet1 ").Range(Target.Address).Value = Target.Value
   Range(("A1"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 0)).Sort _
   key1:=Range("A2"), order1:=xlAscending, header:=xlYes
End Sub

this code will auto sort column A starting from A2

Copy and paste it in the worksheet module

**stevy123** · 04-25-2007, 02:18 PM

Thnak you dave for that reply. Not sure if it is what im looking for though. What i am reading in from the vb program i want to keep random but would like to sort say for example this is the read in data

coloum 1
6
7
1
2
6
7
8

I would like to sort this in excell automatically
coloum 2
1
2
6
6
7
7
8

Anyone any idea on how i could do this?
Any help greatly appriciated.

**davesexcel** · 04-25-2007, 02:31 PM

Originally Posted by stevy123

Thnak you dave for that reply. Not sure if it is what im looking for though. What i am reading in from the vb program i want to keep random but would like to sort say for example this is the read in data

coloum 1
6
7
1
2
6
7
8

I would like to sort this in excell automatically
coloum 2
1
2
6
6
7
7
8

Anyone any idea on how i could do this?
Any help greatly appriciated.

Range(("A1"), Cells(Rows.Count, 1)

change to

Range(("B1"), Cells(Rows.Count, 2)

and

key1:=Range("A2")

to

key1:=Range("B1")

**stevy123** · 04-25-2007, 03:41 PM

Thanks again dave,

Sorry to sound really ungrateful but its not vb i want to do the working out. I am looking to make excell do the working out of sorting the numbers into ascending order and move them from coloum a to coloum b.

Is there a function similar to min(A1:A10)

its somethign like that i am after but to sort the likes of

coloum a
7
8
1
2
3

and put it into coloum b as
1
2
3
7
8

thanks steve

**davesexcel** · 04-26-2007, 04:46 AM

Hi Steve,

Here is one way you could do it, using the large function

Assuming your data starts in row 1

=LARGE($A$1:$A$5,COUNTA(A:A)-(COUNTA(A:A)-ROW()))

This counts the number of values in Column A
Then subtracts the number of values in column A less the row number
ei: if this formula is in B1
counts the number of values in column A
less the # of values in column A less 1
to get 1
the same as

=LARGE($A$1:$A$10,1)
=LARGE($A$1:$A$10,2)
=LARGE($A$1:$A$10,3)
=LARGE($A$1:$A$10,4)
=LARGE($A$1:$A$10,5)

**[email protected]** · 04-03-2012, 07:42 AM

Originally Posted by davesexcel

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Column <> 1 Then Exit Sub
   Worksheets("Sheet1 ").Range(Target.Address).Value = Target.Value
   Range(("A1"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 0)).Sort _
   key1:=Range("A2"), order1:=xlAscending, header:=xlYes
End Sub

this code will auto sort column A starting from A2

Copy and paste it in the worksheet module

hii dave

I am not able to find the worksheet module, please help me.

**davesexcel** · 04-03-2012, 07:52 AM

Originally Posted by [email protected]

hii dave

mujhe worksheet module nahi mil raha hai plz help me

I assume you are asking where the worksheet module is?
Right click on the sheet tab and select View Codes
Paste the code there.

**arlu1201** · 04-03-2012, 08:00 AM

Welcome to the Forum, Deep_cool98, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.