Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort multiple columns, Keep records intact.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort multiple columns, Keep records intact.
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 "ssGuru" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort multiple columns, Keep records intact.
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 -- Regards, Tom Ogilvy "ssGuru" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort multiple columns, Keep records intact.
On Aug 3, 1:06 pm, JNW wrote:
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 "ssGuru" wrote: I have code that autosorts ONE lookup column whenever a value is entered. What change would allow autosorting 3columnsof 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 Definecolumnstoautosortwhen 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 multiplecolumnsand 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 Definecolumnstoautosortwhen 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- Hide quoted text - - 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort multiple columns, Keep records intact.
On Aug 3, 1:30 pm, Tom Ogilvy
wrote: 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 "ssGuru" wrote: I have code that autosorts ONE lookup column whenever a value is entered. What change would allow autosorting 3columnsof 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 Definecolumnstoautosortwhen 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 multiplecolumnsand 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 Definecolumnstoautosortwhen 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- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting columns while keeping row data intact | New Users to Excel | |||
How to copy from Adobe with tables to Excel with columns intact | Excel Discussion (Misc queries) | |||
Sorting columns left to right leave a intact | Excel Programming | |||
Merging 3 Columns in an Excel Spreadsheet (Keeping Data Intact)...? | Excel Programming | |||
Merging 3 Columns in an Excel Spreadsheet (Keeping Data Intact)...2nd Post | Excel Programming |