Sort column in ascending order

sujittalukde

Well-known Member
Joined
Jun 2, 2007
Messages
520
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
Code:
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
This should sort out the fixed range:
Code:
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

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

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

Code:
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

Active Member
Joined
Mar 14, 2002
Messages
469
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

Well-known Member
Joined
Jun 2, 2007
Messages
520
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

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

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
Top