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

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

On Aug 3, 12:12 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
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

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