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?

[Dave Hawley]

Hi


I would suggest a dynamic named range (well worth learning) and then using something like:

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

See: http://www.ozgrid.com/Excel/DynamicRanges.htm

Or a second preference might be:

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


If the growing/shrinking range is column A





_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training


[ This Message was edited by: Dave Hawley on 2002-03-19 07:21 ]

[ This Message was edited by: Dave Hawley on 2002-03-19 07:25 ]

[kojak43]

Thanks for the prompt reply.
I am using '97
I entered your 2nd code and tested. No joy.
Message pop-up says Ambigious Name detected Worksheeet_Change. Help tells me Excel can not find, within VBA, veenlr3.hlp. A search of my hard drive says I have, within VBA, veendf3.hlp.
Guidance?
Oh by the way, went to your web site. I will spend hours there. It seems to be nicely done.

[Dave Hawley]

It just means you already have a Procedure with that name. Delete the old one and paste in mine by, right clicking on the sheet name tab and selecting "View Code"

[kojak43]

Again thanks. Rem'd out the other code. (Thanks for the right click view code tip)

Column "A" range starts at A6.
Column "B" is the field I want sorted.
I changed your code. Mine says "A6" for o/a range and SortRange is "B6"

Test in a new number at "B" and it does not resort after all data has been stroked in and I press enter.

[Dave Hawley]

No too sure I follow waht you have said, but try this:

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


As I said intitially the Dynamic range option would be best.

[Juan Pablo González]

I think I recongize 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

I don't feel (Altough agree with Dave, it's worth learning them !) that a Dynamic range is needed here... just a little object programming:

Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng as Range
Set Rng = Range("A1","B" & Range("A65536").End(xlUp).Row)

If Not Intersect(Target, Rng) Is Nothing Then
Rng.Sort Range("A1"), xlAscending, Header:=xlYes
End If
End Sub

[kojak43]

Thank you both.
It still is not quite right. And I apologize for not providing the following detail previously.
A1:L5 contain various things as instructions, title, date, down arrows for filters etc. The column "headers" appear on A5:L5
Actual data begins on A6:L6
The field that needs to be sorted starts at B6.
Both of your codes sort column B. They (the codes) do not drag the other data along with it. When job number in Column "B" moves to the correct position, it replaces another number in the same column, and the other data stays in the same place. So the job number for one client now has the data for a different client.

My wish list would be to keystroke in all data Ax:Lx (below A5:L5) press enter,tab or mouse click to the next row,and have that entire line of data sort to the correct position based on the number in column "B"

Thanks for your guidance

[Tommy Bak]

Try this one.
This will not sort anything before column L is changed / written to. Then it will sort on column B / A /C from row 6 and down.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 12 And Target.Row > 5 Then
Selection.Sort Key1:=Range("B6"), Order1:=xlAscending, Key2:=Range("A6") _
, Order2:=xlAscending, Key3:=Range("C6"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub

regards Tommy

[kojak43]

Tommy, this code does not seem to work at all.
I can see that it waits for something in column 12 in any row greater than 5, but when I get to 12, and press enter, the entire line stays put.

If only data in columns "A", "B" and "C" are mentioned, how does the other data (the stuff in "E"-"N") know to move when it sorts?