Data auto sorting

[Freshman]

Dear experts,

I've a table of data and the records are filled up by rows. The new records
are copied from other files and pasted to my table. Column A is for clients'
numbers. What I want is whenever new records are pasted to the table, then
all records will be sorted by clients' numbers in ascending order
auotmatically. Please advise how it can be done.

Thanks in advance.

 

[ShaneDevenshire]

Hi Freshman,

You need to use a macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Application.Intersect(Range("A1:A" &
Range("A65536").End(xlUp).Row), Target)
If Not isect Is Nothing Then
Range("A1").CurrentRegion.Sort Key1:=Range("A1"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
End Sub

Put this code in the sheet module for the sheet your data is on. Press Alt
F11 to open the VBE. Double-click the sheet name under your workbook name on
the top left navigation pane.

 

[Freshman]

Hi Shane,

Thanks for your help first. However, when I followed your instruction to
copy the code and then paste it to the sheet which my data is on in VBE (A
large blank space on the right navigation pane with two drop-down arrows
"General" & "Declarations" on the top). When I pasted the code, the colour of
the code statements changed to red. I went back to the worksheet and pasted
some new records under the last record but nothing was changed. When I
clicked one of cells in column A, the screen flashed back to VBE with an
alert dialogue box stated "Syntax Error" appeared. At the same time, a yellow
arrow pointing to the first line of your code and the first line was also
highlighted in gray.

Is there anything wrong I have done so that your code was not working?
Please kindly advise. Sorry for your time spent on my problem.

Regards.

 

[Gord Dibben]

Freshman

You got hit by line-wrap. A couple of the lines should be all one line, not
two.

Try this edited version with added line-continuations "_"

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Application.Intersect(Range("A1:A" & _
Range("A65536").End(xlUp).Row), Target)
If Not isect Is Nothing Then
Range("A1").CurrentRegion.Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
End Sub


Gord Dibben MS Excel MVP

 

[Freshman]

Hi Gord,

Sorry for bother you again. Another problem came up. When I pasted new
records in, an error dialogue box popped up with "Compile error: Variable not
defined". When I removed the "Open Explicit", another box showed up "Compile
error: Named argument not found" and the words of "DataOption1:=" in the
statement were highlighted in gray. Please kindly advise how to solve this.

Thanks & regards.

 

[Dave Peterson]

DataOption# was added in xl2002.

This should(?) may work in previous versions:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

Set iSect = Application.Intersect(Range("A1:A" & _
Range("A65536").End(xlUp).Row), Target)
If Not iSect Is Nothing Then
Range("A1").CurrentRegion.Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub

 

[Freshman]

Hi Dave, Gord & Shane,

Dave has solved my problem. Excellent. You three are all great. Thanks
again. Have a nice day.

Best regards.