CODEWITHSUNDEEP
excelvba
Geographos
Geographos

Reputation: 1466

VBA Excel sort the data from A to Z in one column with all data table changes

It might be such a duplicate question with:

VBA Sort A-Z on One Column

However I want to have the stuff clarified.

I tried to use this code for my purpose:

Sub SortAsc2()

    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "M").End(xlUp).Row

    'Columns("D:D").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("D2:D" & LastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

where I got an error:

1004 Method 'Range' of object_Global failed

I tried another code then

Sub SortDataWithoutHeader()
    Range("D1:D12").Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlNo
End Sub

But the sort happens only within the column, whereas the other data is unaffected. I want to have values from other cells corresponding to the data sort.

enter image description here

Is anyone able to help?

Upvotes: 0

Views: 588

Answers (1)

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

Give this a try.

Read code's comments and adjust it to fit your needs

Code:

Public Sub SortAsc2()

    Dim targetSheet As Worksheet
    Dim targetRange As Range

    Dim lastRow As Long

    ' Set a reference to the sheet
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1")

    ' Find the last non empty row (based on column A)
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row

    ' Set the range to be sorted (A2= begins in row 2 and ends in column K?)
    Set targetRange = targetSheet.Range("A2:K" & lastRow)

    ' Clear current sorting fields
    targetSheet.Sort.SortFields.Clear

    ' You are missing a 1 after "D" in Range in your code
    targetSheet.Sort.SortFields.Add Key:=Range("D1"), _
                                         SortOn:=xlSortOnValues, _
                                         Order:=xlAscending, _
                                         DataOption:=xlSortNormal

    With targetSheet.Sort
        .SetRange targetRange
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Let me know if it works

Upvotes: 1

Related Questions