Autosorting

[kojak43]

Over a month ago someone wrote in about this subject and the follwing code was furnished.

"Using something like this

Code:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
Range("A1:A5").Sort Range("A1"), xlAscending, Header:=xlYes
End If
End Sub


In Here, I'm sorting Range("A1:A5") each time one of the cells changes. I'm also assuming it has a title.

End of supplied code"

My confusion is the range. I would have to increase the range for my sheet, but how can I make the range grow as my rows increase?

 

[Tommy Bak]

ok kojak
I assumed that you did make a change/added data in column L, thats why the macroname is worksheet_change. It selects the entire region and sorts it first by column B, then by A and last by C.
All the other column are sortet with those.
It should be put in the worksheets own codemodule. (rightclick on sheettab, view code, and paste.)
regards tommy

 

[kojak43]

Tommy
You made the correct assumption. However, I forgot that I had two hidden columns. And column 12, sometimes does not get a change. I changed that part of the code from 12 to 14 and it worked.
Well, it worked as far as sorting and moving columns ABC. Data in D:N stayed in the original place.
I know I can manually select ALL of the sheet and then sort to column "B" and it will place what I want where I want it.
I can not seem to manually select just the one new row and have it sort to "B"
Is there a code that selects all of the sheet(from A6:N-current cell), then sorts just column "B"? Right now you have selection.sort, but that seems to select just AB&C.
And as mentioned, that just moves ABC.
I appreciate everyone effort on behalf of this befuddled newby.

 

[Tommy Bak]

I'll try again. please extend the range yourself if A6:N6 is not enough.
regards Tommy

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Column = 14 And Target.Row > 5 Then
Range("A6:N6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub

 

[kojak43]

Tommy
You are my hero!
When I press enter on the last column, it highlghts the spreadsheet from A6:N6 and all the way down. When I press pagedown it brings me down to the bottom of the sheet so I can insert the next job number. It also places the numbers in ascending numerical order.

I hope I am not pressing my luck...is there a way that the cursor does not end up at A6 after I press enter? That is, can it sort and place the cursor in the next opened "A" cell below the last "A" cell that has something in it?

That is, sometime the user needs to enter a number that is not in the correct numerical order. Your code fixes that. Sometimes what she enters is in numberical order and she has no particular need or desire to press the PageDown key as nothing needs to be sorted. However, the code will move the cursor to A6 each time she keys Enter. And she will have to press Down Key before she can enter the next job number.

If it can not be done, I'll understand. I am exceedingly pleased with what you have given me

 

[Tommy Bak]

Yes and thankyou. I'm not sure I can handle so much credit
Place this line just before END SUB

Range("A6").End(xlDown).Offset(1, 0).Select

regards Tommy

 

[kojak43]

Thanks Tommy, it is perfect!

I read this message board frequently and I am always amazed just how fantastic you folks are.

People in the office see some of the stuff you all have given me, and think I am smarter than I am. I give you guys credit, but I don't think anyone here has bothered to open the message board to see for themselves.

Thanks again
K