Need help with auto sorting in Excel

Question

I need Excel to auto sort smallest to largest descending and pull other cells to the new location as a new time is entered.

In detail:

Column C is time of day for a specific event (in military time) beginning on Row 5.  When a user enters a new event and assigns a time to it, I need that corresponding information to sort into the correct event row.  Below is a capture of the worksheet.

So for instance; The data entered for Event #10 beginning with Cell C14 and ending with Cell I14 would need to move to the row for Event #1 (Cells C5 to I5).  So basically I just need Columns "C" through "I" beginning on row 5 to auto sort based off of the time entered in column C so that the proper Event # is met based off of the time of day that it occurs.  I believe this requires a Macro but I am unfamiliar with those.

Answer 1

One caveat before you go too far. . . . .automatic sorting by VBA is risky because if you make an error in data entry it is difficult to track down after the sort occurs and you don't get a "do over"

Why do you feel an autosort is needed?

Selecting columns C to I and Data>Sort takes but a few moments and leaves you with a chance to undo.

VBA code is available if you choose to go that route.

Let us know what you think.

Gord

Answer 2

Thank you, Gord.

This document will be on a shared drive for multiple people to use and I'd prefer not having them all try to do anything other than what the file is intended for (not all of them are well versed with Excel).  Other wise, I would just show them all how to sort after each update or do it myself.  Unfortunately this is not a viable option.  The plan is to have them fill out all of the other pertinent cells first and then enter the time of the event in column C last (since I don't want to sort by anything other than Column C)so that they don't have to track down where the data moved to after it auto sorts.  That being said, there are individual tabs for each day of the week with very short lists to be sorted on each tab so I am not too concerned with entry errors.  It should be fairly simple to recover any lost data due to the small size of the list.

I'd like to at least give the auto sort option a try before resigning myself to manually sorting each time there is an update.

Answer 3

"Shared drive" means one workbook is shared?

You cannot see or edit code in a shared workbook so must unshare before adding the code.

You can re-share it later after you have done your testing and are satisfied with results.

Good idea to make an unshared copy of the workbook for practice workbook.

The code below between the ******************** must be copied then pasted into the Thisworkbook module so's it will affect whichever sheet is currently active.

ALT + F11 to open VBE.

CTRL + r to open Project Explorer.

Select and expand your workbook/project.

Click on Microsoft Excel Objects.

Double-click on Thisworkbook to open.

Paste the code in there then save.

Alt + q to return to the workbook.

*******************************

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim someCells As Range

    With ActiveSheet

    Application.ScreenUpdating = False

        If ActiveCell.Column <> 3 Then Exit Sub

              Set someCells = ActiveSheet.Range("C4", _

              Cells(.Cells.Count))

        With someCells

            .Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlYes, _

             OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

             DataOption1:=xlSortNormal

        End With

    End With

    ActiveSheet.Cells(Rows.Count, 4).End(xlUp) _

            .Offset(1, 0).Select

    Application.ScreenUpdating = True

End Sub

***************************

Good luck

Answer 4

I followed your instruction but I ran into this:

Debug yields:

Answer 5

Here is a worksheet solution:

http://sulprobil.com/Get\_it\_done/IT/Excel\_Fun/Worksheet\_Functions/Sorting/sorting.html

And here a VBA:

http://sulprobil.com/Get\_it\_done/IT/Excel\_Fun/Excel\_VBA/sbGSort/sbgsort.html

Regards,

Bernd