Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort but leave header alone
I have code that nicely sorts any values placed in col 13 and 16 of
some lookup lists. What code change will cause the sort to ignore a header row? 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:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End If End Sub Thanks, Dennis |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort but leave header alone
Header:=xlGuess - Excel tries to figure it out
Header:=xlNo - says there is no header, so sort all rows Header:=xlYes - says there is a header so don't sort that -- Regards, Tom Ogilvy "ssGuru" wrote: I have code that nicely sorts any values placed in col 13 and 16 of some lookup lists. What code change will cause the sort to ignore a header row? 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:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End If End Sub Thanks, Dennis |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort but leave header alone
On Aug 3, 12:12 pm, Tom Ogilvy
wrote: Header:=xlGuess - Excel tries to figure it out Header:=xlNo - says there is no header, so sort all rows Header:=xlYes - says there is a header so don't sort that -- Regards, Tom Ogilvy "ssGuru" wrote: I have code that nicely sorts any values placed in col 13 and 16 of some lookup lists. What code change will cause the sort to ignore a header row? 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:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End If End Sub Thanks, Dennis- Hide quoted text - - Show quoted text - Thanks Tom. Exactly what I needed. Dennis |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort but leave header alone
Try replacing
Header:=xlGuess with either: Header:=xlNo or: Header:=xlYes (I'm not sure what you mean by 'ignore a header row') -- p45cal "ssGuru" wrote: I have code that nicely sorts any values placed in col 13 and 16 of some lookup lists. What code change will cause the sort to ignore a header row? 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:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End If End Sub Thanks, Dennis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autosort in Excel | Excel Discussion (Misc queries) | |||
autosort | Excel Worksheet Functions | |||
Can I AutoSort without refreshing? | Excel Discussion (Misc queries) | |||
Can I AutoSort without refreshing? | Excel Discussion (Misc queries) | |||
AutoSort in VBA | Excel Discussion (Misc queries) |