Automatic sort by colum

juststo · #1 (**permalink**) 09-17-04, 13:30

How can I get a worksheet to sort automatically by a certain column? When I add a new row, one of the columns will be a date. I would like the sheet to automatically sort my new entry in the correct spot.

Also, anyone know of any good (beginner) tutorials for Excel online? I know Excel can do a lot but am having difficulty figuring it out.

shades · #2 (**permalink**) 09-17-04, 15:04

Yes, it is possible to do that, by VBA code. If you can wait a few minutes.

regarding training, there are several good web sites.

http://www.ozgrid.com has many training plans, free Tips and tricks (very helpful), and an excellent forum.

http://www.contextures.com/tiptech.html

The following is a good thought provoking paper about spreadsheet design. Well worth the time.

http://www.mang.canterbury.ac.nz/peo.../spreadsheets/

shades · #3 (**permalink**) 09-17-04, 15:13

Okay, this involves VBA code. This assumes four columns, and the Date is in Column B, with actual dates beginning in B2.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then 'if column B gets changed
    With Range("A1:D" & Range("D65536").End(xlUp).Row)
        .Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With
    End If
End Sub

To place this code, right-click on the worksheet tab (bottom of the worksheet), then choose "View Code". This will open the Visual Basic Editor (VBE) window. Then paste this into that window.

Then go back to your sheet and begin entering data. Make sure that the Date column is formatted as date (choose whatever Date format you want).

[EDIT: Corrected the column number and column reference in second line of code.]

shades · #4 (**permalink**) 09-17-04, 15:15

Quote:

Originally Posted by juststo

I know Excel can do a lot but am having difficulty figuring it out.

Best way is to ask questions and experiment, then ask more questions and experiment some more. There are many who will take the time to walk you through the appropriate steps.

juststo · #5 (**permalink**) 09-17-04, 15:35

Thanks for the advice and code. It works, but it doesn't update if I modify a date. Any ideas???

shades · #6 (**permalink**) 09-17-04, 15:40

In the second line change the 4 to a 2.

Code:

If Target.Column = 2 Then 'if column B gets changed

Internet Services

Web Development

Digital Marketing

Consumer Tech