Table not sorting A-Z when code run

[ipbr21054]

Hi,
The code in use is shown below & does all that is asked BUT dont sort A-Z
The code is on a userform.
My worksheet has a table where headers are in Row N3:S3
The values then go down the page N4:S40

In the code below shown in Red is the N1 correct ?
ALSO
Shown in Blue i see a fixed range,this needs to alter itself as & when values are added / deleted.

Please advise Thanks

    Private Sub CommandButton1_Click()
    Dim lastRow        As Long, i As Long
    Dim wsGIncome      As Worksheet
    Dim arr(1 To 5)    As Variant
    Dim Prompt         As String
   
    Set wsGIncome = ThisWorkbook.Worksheets("G INCOME")
   
    For i = 1 To UBound(arr)
        arr(i) = Choose(i, ComboBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value)
       
        If Len(arr(i)) = 0 Then
            MsgBox "YOU MUST COMPLETE ALL THE FIELDS", vbCritical, "USERFORM FIELDS EMPTY MESSAGE"
            Exit Sub
        End If
    Next i
   
    Application.ScreenUpdating = False
   
    With wsGIncome
        lastRow = .Cells(.Rows.Count, "N").End(xlUp).Row + 1
       
       With .Cells(lastRow, 14).Resize(, UBound(arr))
            .Value = arr
            .Font.Name = "Calibri"
            .Font.Size = 11
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Borders.Weight = xlThin
            .Interior.ColorIndex = 6
           
            .Cells(1, 1).HorizontalAlignment = xlLeft
            Application.ErrorCheckingOptions.BackgroundChecking = False
       End With
           .Range("N4").Select
       
    End With
   
    Unload Me
    Application.ScreenUpdating = True
   
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("N1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("N4:S38")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        MsgBox "DATABASE SUCCESSFULLY UPDATED", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"
    End With
   
End Sub

 

[Domenic]

Try the following...

    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("N3"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("N3").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        MsgBox "DATABASE SUCCESSFULLY UPDATED", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"
    End With

Actually, your code can be re-written as follows...

    With ActiveSheet.Sort
        With .SortFields
            .Clear
            .Add Key:=Range("N3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        End With
        .SetRange Range("N3").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    MsgBox "DATABASE SUCCESSFULLY UPDATED", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"

Hope this helps!

 

[Domenic]

If your data has been converted into a Table (Ribbon >> Insert >> Table), try the following instead...

    With ActiveSheet.ListObjects("Table1").Sort
        With .SortFields
            .Clear
            .Add2 Key:=Range("Table1[[#All],[HeaderName]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        End With
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Change the table name, and the header name accordingly.

Hope this helps!

 

[ipbr21054]

Many thanks all done.
I used the code in post #2