Sort names in tables A-Z

[ipbr21054]

Hi,
I have made some tables but having an issue trying to sort them.
Some info for you.
Worksheet is called INFO
Tables have headers in row 1
GRASS NAME CV2:CV16
GRASS ADDRESS CX2:CX16
GRASS PAID CZ2:CZ16
GRASS MILEAGE DB2:DB16

What im looking to do is place a macro on a button which will sort the names in column CV & keep the correct data in the other columns assigned to each name.

As i add more info to this list the macro would need to update itself with the new range.

Many thanks

 

[kweaver]

You should be able to record a macro to do this. By recording, you'll get the following (but your sheet name will vary) and the green 20 can be whatever you expect the last row to be.
Alternatively, you could calculate the last row in the macro.

Sub SortCV()
'
' SortCV Macro
'

'
    Range("CV1:DB20").Select
    ActiveWorkbook.Worksheets("Sheet7").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet7").Sort.SortFields.Add Key:=Range("CV2:CV7") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet7").Sort
        .SetRange Range("CV1:DB7")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

This:

Grass Name		Address		Paid		Mileage
Weaver		1 Main		100		50
Jones		2 Main		200		60
Brown		3 Main		300		70
Zoro		4 Main		400		25
Abraham		5 Main		500		90

<tbody>
</tbody>

Becomes:

Grass Name		Address		Paid		Mileage
Abraham		5 Main		500		90
Brown		3 Main		300		70
Jones		2 Main		200		60
Weaver		1 Main		100		50
Zoro		4 Main		400		25

<tbody>
</tbody>

 

[kweaver]

Sub SortCV()
'
' SortCV Macro
'

'
    Dim LR As Long
    LR = Cells(Rows.Count, "CV").End(xlUp).Row
    Range("CV1:DB" & LR).Select
    ActiveWorkbook.Worksheets("Sheet7").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet7").Sort.SortFields.Add Key:=Range("CV2:CV" & LR) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet7").Sort
        .SetRange Range("CV1:DB" & LR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Using LR as the last row.

 

[ipbr21054]

Hi,
I have applied the code mentioned above and shown below with the edits added like INFO & Range("CV2").Select

Sub SortCV()'
' SortCV Macro
'


'
    Dim LR As Long
    LR = Cells(Rows.Count, "CV").End(xlUp).Row
    Range("CV1:DB" & LR).Select
    ActiveWorkbook.Worksheets("INFO").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("INFO").Sort.SortFields.Add Key:=Range("CV2:CV" & LR) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("INFO").Sort
        .SetRange Range("CV1:DB" & LR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("CV2").Select
End Sub

It did not sort correctly.

CV2:CV16 NAMES
CX2:CX16 ADDRESS
CZ2:CZ16 PAID
DB2:DB16 MILEAGE

It sorted Names & Mileage A-Z correctly BUT Address & Paid are out of sync

 

[kweaver]

Worked for me...did you change any of the columns?

 

[kweaver]

Add a line:

Sub SortCV()
'
' SortCV Macro
'

'
    Dim LR As Long
    Sheets("INFO").Activate
    LR = Cells(Rows.Count, "CV").End(xlUp).Row
    Range("CV1:DB" & LR).Select
    ActiveWorkbook.Worksheets("INFO").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("INFO").Sort.SortFields.Add Key:=Range("CV2:CV" & LR) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("INFO").Sort
        .SetRange Range("CV1:DB" & LR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

In the event you were on the other sheet when you called the macro.

 

[ipbr21054]

I put the button a few rows below the range that is to be sorted.
Assigned the macro to the button.

Range & button both on sheet INFO.

Just had a thought looking at the columns I mentioned. If a column was hidden between each column would that cause the issue or should it still work ?

 

[kweaver]

As long as the sort range is still from CV to DB it should be fine.

 

[ipbr21054]

Yes the range is fine

 

[ipbr21054]

This morning all is now working