Auto Sorting

[RJ Swain]

Good Afternoon,

I have a question for all you excel experts out there! I am making a
spreadsheet for my staff to use with all the clients they are calling. I
would like to be able to have the spreadsheet auto sort alphabetically by the
name of the person entering in the information. Example:

A1 B1 C1
Row 4 Name Date Time
Row 5 Mark 03-03 1:30pm
Row 6 John 03-03 12:30pm

So when the name is entered Mark would drop to row 6 along with the other
information and john would move up to row 5 with that information as well.
The actual spreadsheet has 6 columns and would have rows 5 - 37 where the
user would enter information.

Thank you

 

[Roger Govier]

Hi

Copy the code below into the relevant Sheet code module.
Right click on the sheet tab
Paste code into white pane
Alt+F11 to return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRange As Range, lr As Long
lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set myRange = Range("F5:F37")

If Not Intersect(Target, myRange) Is Nothing Then
Application.EnableEvents = False
Range("A4:F37").Select
Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A" & lr + 1).Select


End If
Application.EnableEvents = True

End Sub

 

[RJ Swain]

Thank you but I seem to be getting an error:

Run-time error 1004 This operation requires the merged cells to be
identically sized.

So, am I to assume any merged cells I have make them all the same size?

 

[RJ Swain]

Actually, it appears that it is not auto sorting the names, am I missing
something?

 

[Roger Govier]

Hi

Merged cells are a real PIA. They cause all sorts of problems including the
one you have found.
Get rid of all Merged cells.
Use Format>Cells>Alignment>Centre across selection instead, if you are
trying to have text above a number of col in your header