Autosorting

kojak43

Active Member
Joined
Feb 23, 2002
Messages
270
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?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
OzGrid.BusApp.170x45.gif

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
 
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
I think I recongize this code :wink:

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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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
Back
Top