Auto Sort VBA

[esl2427]

Hi!

I have found two old posts that have code that sort of work for my needs, but neither matches entirely. Can anyone help me modify either of these to fit my needs?

Problem: I want to auto sort a worksheet automatically by column A after a new entry has been added. I need the sort to move columns A through I even if a column is left blank. I would prefer it do this after the last entry in the row is made instead of column A or even on saving the workbook.

This code works, but moves the row immediately after data is entered into column A making the user have to scroll up and find it before being able to complete the row entry:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
If Target.Column = 1 Then
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Application.EnableEvents = False
    Range("A1:I" & LR).Sort Key1:=Range("A2"), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Application.EnableEvents = True
End If
End Sub

This code kind of works, but it doesn't allow for any blank entries. Sometimes there will be a blank in one of the columns. But I like that it moves the row after the data is entered in all columns.

 Private Sub Worksheet_Change(ByVal Target As Range)
   If WorksheetFunction.CountA(Cells(Target.Row, 1).Resize(1, 9)) = 9 And Target.Column < 10 Then
        Application.EnableEvents = False
            Range("A1", Cells(Rows.Count, 9).End(xlUp)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
                xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal
        Application.EnableEvents = True
    End If
End Sub

Thanks in advance for any help - it is greatly appreciated!

 

[oldbrewer]

Why not add your new data and then run a simple macro to sort by column A - this macro could be attached to a text box labelled "now sort the table" located a little to the right.

 

[esl2427]

Good idea, but unfortunately isn't ideal for my current situation. I would really prefer to have it automatically sort without the user needing to do anything - hense the VBA code request.

The end users of this workbook are not very computer literate and certainly not Excel savvy. There is a form control button with an assigned macro elsewhere in this workbook that has proved to be a challenge with my end users. Asking them to perform a sort or even adding a button for the marco would be unrealistic at this time.