Sort data by column name - VBA

marc01

New Member
Joined
Sep 17, 2018
Messages
48
Office Version
  1. 2016
Platform
  1. Windows
Hi there,

I have very limited experience with macros and even less with VBA.
I am looking to sort my data based on the column, and thought If i simply recorded my steps using the record macro function in excel that this would be possible.
The problem I am encountering is that the code created is specific to the workbook "200120 Jeff V Bolton", however I would like to be able to use this macro in different worksheets. Furthermore, if this were to work on another sheet I see that the range is to 206 rows, however some sheets may have more rows, some may have less.

Any help would be much appreciated.

I have copied and pasted the code below:

Sub Macro999()
'
' Macro999 Macro
'

'
Range("A1").Select
ActiveWorkbook.Worksheets("200120 Jeffer V Bolton").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("200120 Jefff V Bolton").Sort.SortFields. _
Add2 Key:=Range("A1:M1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:= _
"1st serve,2nd serve,Point won by,Serve outcome,Serve+1 Hand,Serve+1 outcome,Serve+1 Situation,Server,Side" _
, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("200120 Jeff V Bolton").Sort
.SetRange Range("A1:M206")
.Header = xlYes
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Columns("J:M").Select
Selection.Delete Shift:=xlToLeft
End Sub
 

Some videos you may like

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.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
I haven't tested this macro, but you will notice that I have used "ActiveSheet" and "LastRow". This macro should work on any sheet regardless of the number of rows as long as it is the active sheet.
VBA Code:
Sub SortData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("A1:M1"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
            "1st serve,2nd serve,Point won by,Serve outcome,Serve+1 Hand,Serve+1 outcome,Serve+1 Situation,Server,Side", DataOption:=xlSortNormal
        .SetRange Range("A1:M" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("J:M").Delete Shift:=xlToLeft
    Application.ScreenUpdating = True
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,588
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub marc()
   With ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add Range("A1:M1"), xlSortOnValues, xlAscending, _
         "1st serve,2nd serve,Point won by,Serve outcome,Serve+1 Hand,Serve+1 outcome,Serve+1 Situation,Server,Side"
      .SetRange Range("A1").CurrentRegion
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlLeftToRight
      .Apply
   End With
End Sub
 

marc01

New Member
Joined
Sep 17, 2018
Messages
48
Office Version
  1. 2016
Platform
  1. Windows
I haven't tested this macro, but you will notice that I have used "ActiveSheet" and "LastRow". This macro should work on any sheet regardless of the number of rows as long as it is the active sheet.
VBA Code:
Sub SortData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("A1:M1"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
            "1st serve,2nd serve,Point won by,Serve outcome,Serve+1 Hand,Serve+1 outcome,Serve+1 Situation,Server,Side", DataOption:=xlSortNormal
        .SetRange Range("A1:M" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("J:M").Delete Shift:=xlToLeft
    Application.ScreenUpdating = True
End Sub
Brilliant! This works perfectly - many thanks.
 

marc01

New Member
Joined
Sep 17, 2018
Messages
48
Office Version
  1. 2016
Platform
  1. Windows
How about
VBA Code:
Sub marc()
   With ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add Range("A1:M1"), xlSortOnValues, xlAscending, _
         "1st serve,2nd serve,Point won by,Serve outcome,Serve+1 Hand,Serve+1 outcome,Serve+1 Situation,Server,Side"
      .SetRange Range("A1").CurrentRegion
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlLeftToRight
      .Apply
   End With
End Sub
Many thanks - this does order the columns correctly as well, however the code above from mumps does also delete the final 4 columns also. I am going to play about with both codes so i can learn from them.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,588
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,685
Messages
5,626,280
Members
416,171
Latest member
cfrenomaly

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
Top