auto sort (easyily)

  • Thread starter Thread starter denv
  • Start date Start date
D

denv

Ive already asked, but there must be an easier way. If there isn't
please return a code with the cell refs I use. I have a table from
B4:J9 which I want to sort as soon as any of the data changes. I want
to sort it by the J column section of it. Is there a way to do this as
the code just confuses me>
 
right click on sheet tab>view code>insert this>save workbook
Assumes you have titles in row 4 and data in other rows and will fire when a
change is made in j5:j9

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("b5:j9")) Is Nothing Then Exit Sub
Range("A4:j9").Sort Key1:=Range("j5"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
End Sub
 
Denv

Something like the code below, placed in the code module behind a worksheet
would work. It uses the Worksheet_Change() event to check if the change is
made in the desired range (B4:J9) and if it is, it sorts that range by
column J

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo EventEnable
With Application
If Not .Intersect(Target, Range("B4:J9")) Is Nothing Then
.EnableEvents = False
Range("B4:J9").Sort Key1:=Columns("J:J")
.EnableEvents = True
End If
End With
Exit Sub
EventEnable:
Application.EnableEvents = True
Exit Sub
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Don

I never tried it, but presumed the sort would fire another change event?

(Scurries off to check....)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)


Don Guillett said:
right click on sheet tab>view code>insert this>save workbook
Assumes you have titles in row 4 and data in other rows and will fire when a
change is made in j5:j9

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("b5:j9")) Is Nothing Then Exit Sub
Range("A4:j9").Sort Key1:=Range("j5"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
End Sub
Click to expand...
 
Don

No, you're right....

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4:J9")) Is Nothing Then
Range("B4:J9").Sort Key1:=Columns("J:J")
End If
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)


Don Guillett said:
right click on sheet tab>view code>insert this>save workbook
Assumes you have titles in row 4 and data in other rows and will fire when a
change is made in j5:j9

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("b5:j9")) Is Nothing Then Exit Sub
Range("A4:j9").Sort Key1:=Range("j5"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
End Sub
Click to expand...
 
You got good answers using the worksheet_change event, but as a user, I don't
think I'd like this.

If I were entering data and made a change and then went to the next cell, I
might not be in the spot I really wanted to be in since the sort could have
rearranged all my data.

I think I'd steal the sort code and put it in a separate macro. Then I'd put a
button from the forms toolbar on the sheet near that range that read: "Click me
to sort the table". (and assign that macro to that button.)
 
OP
Dave Peterson said:
You got good answers using the worksheet_change event, but as a user, I don't
think I'd like this.

If I were entering data and made a change and then went to the next cell, I
might not be in the spot I really wanted to be in since the sort could have
rearranged all my data.

I think I'd steal the sort code and put it in a separate macro. Then I'd put a
button from the forms toolbar on the sheet near that range that read: "Click me
to sort the table". (and assign that macro to that button.)
Click to expand...
 
And since the sort is on column J, the code could react to a change in
that column only:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J4:J9")) Is Nothing Then
Range("B4:J9").Sort Key1:=Columns("J:J")
End If
End Sub
 
Ah yes, beware of what you ask cuz you might just get it
"Beware Of What You Ask For"

An atheist was taking a walk through the woods, admiring all that the
"accident of evolution" had created. " What majestic trees! What powerful
rivers! What beautiful animals! " he said to himself.

As he was walking alongside the river he heard a rustling in the bushes
behind him.
He turned to look.
He saw a 7-foot grizzly charge towards him.

He ran as fast as he could up the path.
He looked over his shoulder and saw that the bear was closing in.

He ran even faster, so scared that tears were coming to his eyes.
He looked over his shoulder again, and the bear was even closer.
His heart was pumping frantically and he tried to run even faster.

He tripped and fell on the ground. He rolled over to pick himself up but saw
the bear, right on top of him; reaching for him with his left paw and
raising his right paw to strike him.

At that instant the Atheist cried out "Oh my God!..."

Time stopped. The bear froze. The forest was silent. Even the river stopped
moving.
As a bright light shone upon the man, a voice came out of the sky,

"You deny my existence for all of these years; teach others I don't exist;
and even credit creation to a cosmic accident. Do you expect me to help you
out of this predicament? Am I to count you as a believer?"

The atheist looked directly into the light "It would be hypocritical of me
to suddenly ask You to treat me as a Christian now, but could you perhaps
make the bear a Christian?"

"Very well," said the voice.

The light went out. The river ran again. And the sounds of the forest
resumed. And then the bear dropped his right paw ... brought both paws
together...bowed his head and spoke: "Lord, for this food which I am about
to receive, I am truly thankful
Dave Peterson said:
Yep. I saw that and remarked that the OP got what he asked for. But it may not
be what he/she wants.
Click to expand...
 
Back
Top