Autosort multiple columns, Keep records intact.

[ssGuru]

I have code that autosorts ONE lookup column whenever a value is
entered.

What change would allow autosorting 3 columns of records but keep
everything ordered by the 1st column and not loose syncing of the
records?

This code perfectly sorts any values placed in col 13 OR 16 of some
lookup lists.

Private Sub Worksheet_Change(ByVal Target As Range)
'DD Define columns to autosort when record added or deleted
If Target.Column = 13 Or Target.Column = 16 Then
Columns(Target.Column).Sort _
Key1:=Cells(1, Target.Column), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub

I couldn't find a Class property that delt with the issue of sorting
multiple columns and keeping the records intact so I tried this code
change but it did nothing at all that I could see:

Private Sub Worksheet_Change(ByVal Target As Range)
'DD Define columns to autosort when record added or deleted
If Target.Column = 26 And Target.Column = 27 _
And Target.Column = 28 And Target.Column = 29 Then
Columns(Target.Column).Sort _
Key1:=Cells(1, Target.Column), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub


Thanks, Dennis

 

[JNW]

I'm not able to try this out at the moment, but you would need to add code
that would select the rows that have the same identifying column (I think you
said it was the first) then only sort those rows. This would be done after
you sorted for the 1st column.

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Range)
'DD Define columns to autosort when record added or deleted
If Target.Column = 13 Or Target.Column = 16 Then
Columns(Target.Column).Resize(,3).Sort _
Key1:=Cells(1, Target.Column), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub

 

[ssGuru]

I'm not able to try this out at the moment, but you would need to add code
that would select the rows that have the same identifying column (I think you
said it was the first) then only sort those rows. This would be done after
you sorted for the 1st column.
--
JNW











- Show quoted text -

Say I have 4 consecutive columns of records. I happen to use the 1st
column as a lookup table.
Col1, Col2, Col3, Col4
Dogs, 5, Black, 20%

I want to autosort on data entry and keep the columns relationships
together whenever a new value is added to the 1st column

So when I add a new value to Col1 the autosort code would place the
value correctly in Col1.
Col1, Col2, Col3, Col4
Cats
Dogs, 5, Black, 20%

I can then edit Col2, 3 and 4 for the Cats entry. (later I would like
to force a value in col 2, 3 and 4 BEFORE the autosort but one step at
a time)


However when the 1st column auto resorts I don't want columns 2, 3 and
4 to become scrambled in relation to the 1st columnm as would occur if
ONLY column 1 was sorted, so I can keep the records intact. The code
to sort one col is above and works fine. Need help with keeping
columns 2, 3 and 4 records in their proper place together with column
1.

Thanks for the help,
Dennis

 

[ssGuru]

Private Sub Worksheet_Change(ByVal Target As Range)
'DD Definecolumnstoautosortwhen record added or deleted
If Target.Column = 13 Or Target.Column = 16 Then
Columns(Target.Column).Resize(,3).Sort _
Key1:=Cells(1, Target.Column), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub

--
Regards,
Tom Ogilvy











- Show quoted text -

Thanks Tom,
Here is the code I ended up with that handles BOTH autosorting
selected single columns with headers OR autosorting the 1st column of
selected multiple colums (4wide and 3wide) with headers, while keeping
the records intact. Works perfectly.

This solves the issue of keeping the lookup lists perfectly sorted
regardless of changes made by the user.

Private Sub Worksheet_Change(ByVal Target As Range)
'DD Define multiple autosort cols when record added or deleted
If Target.Column = 5 Then
Columns(Target.Column).Resize(, 3).Sort _
Key1:=Cells(1, Target.Column), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End If

'DD Define multiple autosort cols when record added or deleted
If Target.Column = 13 Then
Columns(Target.Column).Resize(, 4).Sort _
Key1:=Cells(1, Target.Column), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End If

'DD Define single autosort cols when record added or deleted
If Target.Column = 9 Or Target.Column = 11 Then
Columns(Target.Column).Sort _
Key1:=Cells(1, Target.Column), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End If

End Sub