automatic sorting in excel using macros

J

Julica

Active Member

Joined

Dec 17, 2002

Messages

267

• Mar 24, 2003

• #2

Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Range("A1:D4").Select 'this is the sort range
    Selection.sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        
    Target.Select
End Sub

in the worksheet pane should get you started. Tweak as necessary.

 

Upvote 0

C

CUP

New Member

Joined

Mar 24, 2003

Messages

3

• Mar 24, 2003

• #3

Thanks.......

CUP

 

Upvote 0

C

CUP

New Member

Joined

Mar 24, 2003

Messages

3

• Mar 24, 2003

• #4

Hey.....

It is working but the problem is if I want to add one complete row then it should sort after I am done with adding new row. But here the code is activating in the middle of adding a new row..do you have any idea how to overcome this.........

CUP.

 

Upvote 0

J

Julica

Active Member

Joined

Dec 17, 2002

Messages

267

• Mar 25, 2003

• #5

Oops! Not sure how to handle that. Maybe some of the REAL gurus out there can help. I'm just a pseudo-guru.

Now my secret's out!

 

Upvote 0

Tom Urtis

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,300

• Mar 25, 2003

• #6

Assumes column A with header in A1. Right click on sheet tab, left click on view code, paste in below code; click Alt+Q to return to worksheet.

Tested on XL2K2.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Range(("A1"), Cells(Rows.Count, 1).End(xlUp)).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=True, Orientation:=xlTopToBottom
End Sub

 

Upvote 0

J

Jadre

New Member

Joined

Feb 26, 2014

Messages

18

• Mar 4, 2014

• #7

i tried your macro and it worked fine when i changed them in the range but could u make one that would sort too when using the = formula

for example the range is A2:A10 and there is a cell outside that range that i connected to a cell in the range and when i change the cell outside the range the same number will be in the range but it doesn't sort automatically until i change another cell in the range

 

Upvote 0