Alphabetical order possible? or not

[OSSIE]

can you have excel as you enter data arrange it in alphabetical order b
last names or names

 

[Gord Dibben]

Using event code I'm sure you could get Excel to sort your entries as you
entered them, but I would find that very annoying.

I prefer to enter my names in a column in any order and when done, sort that
column at one go.

But here is some code to sort as you enter.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
If .Value <> "" Then
Call Macro2
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Sub Macro2()
Range("A:A").Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the event code and the macro2 into that module.

Close that module and hit ALT + Q to go back to Excel.

Start typing names in column A


Gord Dibben MS Excel MVP

 

[Guest]

DATA | CLASSIFY

"OSSIE" escreveu:

 

[OSSIE]

Using event code I'm sure you could get Excel to sort your entries as
you
entered them, but I would find that very annoying.

I prefer to enter my names in a column in any order and when done, sort
that
column at one go.

But here is some code to sort as you enter.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
If .Value <> "" Then
Call Macro2
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Sub Macro2()
Range("A:A").Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the event code and the macro2 into that module.

Close that module and hit ALT + Q to go back to Excel.

Start typing names in column A


Gord Dibben MS Excel MVP

Hi and thnak you for your help
one more question on this last name and name have to be in the same
colum correct co is it possible to have the last name on column A and
the name on B ?

 

[u0107]

One way that I normally do is:

1) Define a Parameters Tab where all parameters are stored.
2) In this Parameters Tab, in one of the cells enter a formula using
Countif over a suitably large range (say 3000 rows when you expect to
have about 1000 or slightly more rows), which tells you how many rows
of names are populated.
3) Using Insert-Name-Define, name this cell suitably say
No_of_name_rows.
4) go back to your tab and create a button.
5) Using Record Macro actually sort your name list while recording your
macro.
6) Modify the recorded macro (after stopping) to use the value of the
No_of_name_rows which you defined in step 3.
7) Associate the macro with the button you created in step 4.

Now you can enter a few names and click the button and get the data
sorted.

As far as getting first name and last name in columns A and B but using
a combination of First and last name, you can do that by using
Concatenate function in the corresponding cell in Column C on the same
row.

Please write to me in case of questions.

Uttam

 

[Gord Dibben]

Try this modification.

Either set your ENTER key to move right upon entry or use the Tab key to move
right out of column A to B

Enter last name in col A and first name in col B.

When you hit ENTER out of col B, A and B will sort by Last Name

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B1:B100")) Is Nothing Then
With Target
If .Value <> "" Then
Call Macro2
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Sub Macro2()
Range("A:B").Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub


Gord