CODEWITHSUNDEEP
vbaexcelsorting
MBrann
MBrann

Reputation: 265

Sort Values A to Z

Hi I have created a code which adds values to certain ranges. These ranges already have a formula which allows the range to become dynamic.

What I am wanting to do is to sort just one of these named ranges A to Z which is called "Name".

The issue I am having is, it appears to sort but does not actually sort any of the values. Can someone please help?

Below is my code

Private Sub CommandButton1_Click()

Dim LRow As Long
Dim BRow As Long

Dim ws6 As Worksheet

Application.ScreenUpdating = False

Set ws6 = Worksheets("Lookup Vals")

    LRow = ws6.Range("C:C").Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

    ws6.Cells(LRow, 3).Value = Me.tbLNName.Value

    Select Case True
    Case Me.CBLNComp.Value = "Type1": BRow = ws6.Range("L:L").Find(What:="*", SearchOrder:=xlRows, _
                                                SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        ws6.Cells(BRow, 12).Value = Me.tbLNName.Value
        ws6.Cells(BRow, 13).Value = Me.tbLNBP.Value
        ws6.Cells(BRow, 14).Value = Me.CBLNComp.Value

    Case Me.CBLNComp.Value = "Type2": BRow = ws6.Range("P:P").Find(What:="*", SearchOrder:=xlRows, _
                                                SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        ws6.Cells(BRow, 16).Value = Me.tbLNName.Value
        ws6.Cells(BRow, 17).Value = Me.tbLNBP.Value
        ws6.Cells(BRow, 18).Value = Me.CBLNComp.Value

    End Select

Application.ScreenUpdating = True

With ActiveWorkbook.Worksheets("Lookup Vals").AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Unload AddBP

End Sub

Upvotes: 0

Views: 62

Answers (1)

Xabier
Xabier

Reputation: 7735

I believe the following should do the trick, you failed to state the range you wanted the sort to apply to:

Private Sub CommandButton1_Click()
Dim LRow As Long
Dim BRow As Long
Dim ws6 As Worksheet: Set ws6 = ThisWorkbook.Worksheets("Lookup Vals")

Application.ScreenUpdating = False

    LRow = ws6.Range("C:C").Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

    ws6.Cells(LRow, 3).Value = Me.tbLNName.Value

    Select Case True
    Case Me.CBLNComp.Value = "Type1": BRow = ws6.Range("L:L").Find(What:="*", SearchOrder:=xlRows, _
                                                SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        ws6.Cells(BRow, 12).Value = Me.tbLNName.Value
        ws6.Cells(BRow, 13).Value = Me.tbLNBP.Value
        ws6.Cells(BRow, 14).Value = Me.CBLNComp.Value

    Case Me.CBLNComp.Value = "Type2": BRow = ws6.Range("P:P").Find(What:="*", SearchOrder:=xlRows, _
                                                SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        ws6.Cells(BRow, 16).Value = Me.tbLNName.Value
        ws6.Cells(BRow, 17).Value = Me.tbLNBP.Value
        ws6.Cells(BRow, 18).Value = Me.CBLNComp.Value

    End Select

Application.ScreenUpdating = True

    ws6.Sort.SortFields.Clear
    ws6.Sort.SortFields.Add Key:=Range("Name"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws6.Sort
        .SetRange Range("Name")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Unload AddBP
End Sub

Upvotes: 1

Related Questions