Sort column in ascending order

[sujittalukde]

How auto ascending sort can be made?
I need it for two column Col A & B
The user will put data in cells of col A & B
After entering data in cell B, when the user will press enter, the
data should be sorted in ascending order based on col A and to
expand the selection to column B.
I reocrded a macro to do this but it has certain problems

1. THe range is fixed
2. It doesnot run automatically as I stated earlier.

The code is

Sub sortasc()

Range("A2:B8").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, 
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, 
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
End Sub

 

[Lewiy]

This should sort out the fixed range:

Sub sortasc()
Range("A2:B" & Cells(Rows.Count, 2).End(xlUp).Row).Sort Key1:=Range("A2"), Order1:=xlAscending, _
    Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
End Sub

What exactly would you want to be the trigger for the sort? Just pressing enter is probably no good because I can't imagine you want this to sort every time enter is pressed.

 

[Domski]

Hi,

This will kick off each time a cell in column B changes:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRange As Range

Set myRange = Range([b2], [b65536].End(xlUp))

If Not Intersect(Target, myRange) Is Nothing Then

Range("A2").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("A1").Select

End If

End Sub

Right click on the sheet tab and select view code, then paste the above onto it.

Dom

 

[ammdumas]

What about putting the code into the following sub in MyWorkbook?

Private Sub Worksheet_Change(ByVal Target As Range)
'your code
End Sub

You could add the necessary If statement to keep it from running only on certain circumstances...

 

[sujittalukde]

Thanks to duo for the reply

Dear Lewiy, actually I need the same for occasional entries so cell change event will not disturb too much. Anyway thanks for the reply and the effort.

 

[brian.wethington]

Would it not work for all intents and puposes to use a before save event rather than a change. Sometimes using the change event in this way can confuse the user, depending on the user of course. If you are the only user this won't matter. Just a thought.