How to auto Sort list after entering last item in a row.

[Craig]

Hi,

I have an excel list with 9 columns, the first heading is date, the last is
total.

I need to enter a batch of records into separate rows under the headings,
with each record having a different date. Is there any way that the entire
list could be sorted by date every time I press enter after entering all the
data in the list (I dont want to click the A to Z icon each row or at the end
of data entry if possible.

Note: the last column sums several cells within the data record.

Thanks for any help on this.

Craig

[Tom Ogilvy]

Assuming headers are in row1, a1:I1

Right click on sheet tab and select view code.

put in code similar to this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo ErrHandler
If IsEmpty(Target) Then Exit Sub
If Target.Column = 9 Then
Application.EnableEvents = False
Set rng1 = Range("A1").CurrentRegion
Set rng1 = rng.Resize(rng1.Rows.Count - 1)
rng1.Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlYes
End If
ErrHandler:
Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy

"Craig" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have an excel list with 9 columns, the first heading is date, the last
is
> total.
>
> I need to enter a batch of records into separate rows under the headings,
> with each record having a different date. Is there any way that the
entire
> list could be sorted by date every time I press enter after entering all
the
> data in the list (I dont want to click the A to Z icon each row or at the
end
> of data entry if possible.
>
> Note: the last column sums several cells within the data record.
>
> Thanks for any help on this.
>
> Craig
>

[Craig]

Thank you Tom...I put in this exact code into the code window and tried but
it did not automatically sort the list after I pressed enter in column I.

Any thoughts?

Appreciate any help...thanks very much

Craig

"Tom Ogilvy" wrote:

> Assuming headers are in row1, a1:I1
>
> Right click on sheet tab and select view code.
>
> put in code similar to this:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count > 1 Then Exit Sub
> On Error GoTo ErrHandler
> If IsEmpty(Target) Then Exit Sub
> If Target.Column = 9 Then
> Application.EnableEvents = False
> Set rng1 = Range("A1").CurrentRegion
> Set rng1 = rng.Resize(rng1.Rows.Count - 1)
> rng1.Sort Key1:=Range("A1"), Order1:=xlAscending, _
> Header:=xlYes
> End If
> ErrHandler:
> Application.EnableEvents = True
>
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Craig" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I have an excel list with 9 columns, the first heading is date, the last
> is
> > total.
> >
> > I need to enter a batch of records into separate rows under the headings,
> > with each record having a different date. Is there any way that the
> entire
> > list could be sorted by date every time I press enter after entering all
> the
> > data in the list (I dont want to click the A to Z icon each row or at the
> end
> > of data entry if possible.
> >
> > Note: the last column sums several cells within the data record.
> >
> > Thanks for any help on this.
> >
> > Craig
> >
>
>
>