excel automatically Alphabetical

Question

people, my doubts is simple .. I created a spreadsheet in excel with several fields, eg:

NAME       ADDRESS      PHONE
carlos      ave. 1    12345678
Argeu       av .2     87654321

After this, I used the following code in the module:

Private Sub Worksheet_Activate()
ActiveSheet.ScrollArea = "$A$2:$A$300"
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range("$A$2:$A" & LR).Sort Key1:=Range("$A$2")
End Sub

This code will make in the example table, the Argeu stand on the carlos, so far so good, but I want when the Argeu is up from carlos the phone and address data also rise .. If anyone can help, thank you

Siddharth Rout · Accepted Answer · 2013-11-24T22:43:41.413

0

IF you want Col C to be also sorted then include that in the sort range as well. See this.

Change

Range("$A$2:$A" & LR).Sort Key1:=Range("$A$2")

to

Range("$A$2:$C" & LR).Sort Key1:=Range("$A$2")

EDIT

Your code can be properly re-written as

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False

    '~~> For xl2007+ use .CountLarge
    If Target.Cells.CountLarge > 1 Then Exit Sub
    '~~> For xl2003 use .Count
    'If Target.Cells.Count > 1 Then Exit Sub

    Dim LR As Long

    If Application.WorksheetFunction.CountA(Cells) <> 0 Then
        LR = Cells.Find(What:="*", _
                      After:=Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        LR = 1
    End If

    Range("$A$2:$C" & LR).Sort Key1:=Range("$A$2")

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

ScreenShot

enter image description here

edited Nov 24 '13 at 22:43

answered Nov 24 '13 at 22:37

Siddharth Rout

147,039
17
206
250

It tested it and it worked for me... updating my post – Siddharth Rout Nov 24 '13 at 22:41
wait, I'll post a reply with some prints – Gabriel Sarates Nov 24 '13 at 22:44
1° http://prntscr.com/26jjuj 2° http://prntscr.com/26jjmh 3° http://prntscr.com/26jk0s this is what happens when I put the first A, then C and finally B – Gabriel Sarates Nov 24 '13 at 22:48
Where did that `B` come from? Did you enter it somewhere far below? – Siddharth Rout Nov 24 '13 at 22:51
More about `worksheet_Change` [HERE](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) Err, can we keep FB out of this :) – Siddharth Rout Nov 24 '13 at 22:55

excel automatically Alphabetical

1 Answers1