Auto Sort VBA

esl2427

New Member
Joined
Apr 22, 2013
Messages
3
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:
Code:
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.

Code:
 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!
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
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

New Member
Joined
Apr 22, 2013
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,556
Messages
5,596,820
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top