Sort names in tables A-Z

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,738
Office Version
  1. 2007
Platform
  1. Windows
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
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.

Rich (BB code): 
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:


<tbody>
[TD="class: xl65"]Grass Name[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]Address[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]Paid[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]Mileage[/TD]

[TD="class: xl65"]Weaver[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1 Main[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]50[/TD]

[TD="class: xl65"]Jones[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]2 Main[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]60[/TD]

[TD="class: xl65"]Brown[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]3 Main[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]300[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]70[/TD]

[TD="class: xl65"]Zoro[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]4 Main[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]400[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]25[/TD]

[TD="class: xl65"]Abraham[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]5 Main[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]500[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]90[/TD]

</tbody>


Becomes:


<tbody>
[TD="class: xl65"]Grass Name[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]Address[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]Paid[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]Mileage[/TD]

[TD="class: xl65"]Abraham[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]5 Main[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]500[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]90[/TD]

[TD="class: xl65"]Brown[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]3 Main[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]300[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]70[/TD]

[TD="class: xl65"]Jones[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]2 Main[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]60[/TD]

[TD="class: xl65"]Weaver[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1 Main[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]50[/TD]

[TD="class: xl65"]Zoro[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]4 Main[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]400[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]25[/TD]

</tbody>
 
Last edited:
Upvote 0
Code: 
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.
 
Upvote 0
Hi,
I have applied the code mentioned above and shown below with the edits added like INFO & Range("CV2").Select
Code: 
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
 
Upvote 0
Add a line:

Rich (BB code): 
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.
 
Last edited:
Upvote 0
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 ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Sort table out of sync
Replies
0
Views
308
ipbr21054
I
I
  • Question Question
Advice to sort table after values have been pasted
Replies
1
Views
369
Alex Blakenburg
A
I
  • Solved
Table not sorting A-Z when code run
Replies
3
Views
479
ipbr21054
I
P
  • Solved
create a report summary page by group
Replies
4
Views
202
PippaThePointer
P
I
  • Question Question
Receive Automation error when running code & values added to sheet twice
2
Replies
13
Views
430
NoSparks
N

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top