Using VBA to Sort A Named Range

[Chris Waller]

Dear All,

I am trying to create some VBA that will enable me to sort a number of Spreadsheets that come to me each month. The Spreadsheets are all in the same layout, however, the length of the Spreadsheets vary from only a couple of rows to several hundred rows. The problem is that the code below which I recorded refers to specific rows. From what I have read, it appears that I need to use a named range to facilitate the varying number of rows. The problem is that I don't know how to modify the code below to do this. I would be grateful if someone could look at the code and let me know where I have to change this. TIA

Sub Sort_By_Date_and_Name()
'
' Macro2 Macro
'

'
Application.Goto Reference:="R2C7"
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
ActiveWorkbook.Names.Add Name:="MyData", RefersToR1C1:= _
"=Output!R2C1:R109C7"
ActiveWorkbook.Worksheets("Output").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Output").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, -5).Range("A1:A108"), Sorton:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Output").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, -6).Range("A1:A108"), Sorton:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Output").Sort
.SetRange ActiveCell.Offset(-1, -6).Range("A1:G109")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[adkock]

You need to add two variables, startrow and endrow that you can use to define the rows to be sorted

Sub Sort_By_Date_and_Name()
 '
 ' Macro2 Macro
 '
Dim StartRow as Integer
Dim EndRow as Integer
Dim SortSheet as String
Dim FirstKeyCol as String
Dim SecondKeyCol as String
Dim FirstSortCol as String
Dim LastSortCol as String
 '
StartRow = 1
EndRow = Range("A65000").End(xlUp).Row 'Assuming column A is always filled
SortSheet = "Output"
FirstKeyCol = "B"
SecondKeyCol = "A"
FirstSortCol = "A"
LastSortCol = "G"

 ActiveWorkbook.Worksheets(SortSheet ).Sort.SortFields.Clear
 ActiveWorkbook.Worksheets(SortSheet ).Sort.SortFields.Add Key:=ActiveCell. _
 Range(FirstKeyCol & StartRow & ":" & FirstKeyCol & EndRow), Sorton:=xlSortOnValues, Order:=xlAscending, _
 DataOption:=xlSortNormal
 ActiveWorkbook.Worksheets(SortSheet).Sort.SortFields.Add Key:=ActiveCell. _
 Range(SecondKeyCol & StartRow & ":" & SecondKeyCol & EndRow), Sorton:=xlSortOnValues, Order:=xlAscending, _
 DataOption:=xlSortNormal
 With ActiveWorkbook.Worksheets(SortSheet).Sort
 .SetRange ActiveCell.Range(FirstSortCol & StartRow & ":" & LastSortCol & EndRow)
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 End With
 End Sub

 

[Chris Waller]

adkock,

Thanks for your prompt reply. There does however, appear to be a slight problem. That is because there is always going to be data in column A below the range I need to sort. Currently the VBA is picking up this data and sorting it along with the range that I acually need sorting. Thanks again.

 

[adkock]

Is this data continuous? or is there a blank line after the block you want to sort?

 

[Chris Waller]

Immediately below the data (No space) there is a total line. Because this row does not go across to column G I used this as the starting point. Below the total line there are a couple of enpty rows then some other figures, again these need to be excluded from the range. It is for these reasons I am trying to use a named range.

 

[adkock]

I have changed the line

EndRow = Range(LastSortCol  & StartRow).End(xlDown).Row 'Assuming column G is always filled and does not include the total rule

In this way, the endrow is determined from column C. The entire code then becomes:

Sub Sort_By_Date_and_Name()
 '
 ' Macro2 Macro
 '
Dim StartRow as Integer
Dim EndRow as Integer
Dim SortSheet as String
Dim FirstKeyCol as String
Dim SecondKeyCol as String
Dim FirstSortCol as String
Dim LastSortCol as String
 '
StartRow = 1
SortSheet = "Output"
FirstKeyCol = "B"
SecondKeyCol = "A"
FirstSortCol = "A"
LastSortCol = "G"
EndRow = Range(LastSortCol  & StartRow).End(xlDown).Row 'Assuming column G is always filled and does not include the total rule

 ActiveWorkbook.Worksheets(SortSheet ).Sort.SortFields.Clear
 ActiveWorkbook.Worksheets(SortSheet ).Sort.SortFields.Add Key:=ActiveCell. _
 Range(FirstKeyCol & StartRow & ":" & FirstKeyCol & EndRow), Sorton:=xlSortOnValues, Order:=xlAscending, _
 DataOption:=xlSortNormal
 ActiveWorkbook.Worksheets(SortSheet).Sort.SortFields.Add Key:=ActiveCell. _
 Range(SecondKeyCol & StartRow & ":" & SecondKeyCol & EndRow), Sorton:=xlSortOnValues, Order:=xlAscending, _
 DataOption:=xlSortNormal
 With ActiveWorkbook.Worksheets(SortSheet).Sort
 .SetRange ActiveCell.Range(FirstSortCol & StartRow & ":" & LastSortCol & EndRow)
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 End With
 End Sub

This should work...

 

[Chris Waller]

adkock,

Thanks for your help again, however, the code still refuses to run. I have tried to run the code using the F8 key and when the cursor gets to the ActiveWorkbook.Worksheets line a pop-up box appears stating Run-time error '9': Subscript out of range. As I have no idea what this means, I would appreciate your help once again.

 

[adkock]

Chris Waller,

I have slightly adapted the code:

Sub Sort_By_Date_and_Name()
 '
 ' Macro2 Macro
 '
Dim StartRow As Integer
Dim EndRow As Integer
Dim SortSheet As String
Dim FirstKeyCol As String
Dim SecondKeyCol As String
Dim FirstSortCol As String
Dim LastSortCol As String
 '
StartRow = 1
SortSheet = "Output"
FirstKeyCol = "B"
SecondKeyCol = "A"
FirstSortCol = "A"
LastSortCol = "G"
EndRow = Range(LastSortCol & StartRow).End(xlDown).Row  'Assuming column G is always filled and does not include the total rule


Range(FirstSortCol & StartRow & ":" & LastSortCol & EndRow).Select
Worksheets(SortSheet).Sort.SortFields.Clear
Worksheets(SortSheet).Sort.SortFields.Add Key:=Range(FirstKeyCol & StartRow + 1 & ":" & FirstKeyCol & EndRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Worksheets("Output").Sort.SortFields.Add Key:=Range(SecondKeyCol & StartRow + 1 & ":" & SecondKeyCol & EndRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Worksheets(SortSheet).Sort
        .SetRange Range(FirstSortCol & StartRow & ":" & LastSortCol & EndRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With
End Sub

Run time error 9 indicates that a subscript is out of range. This implies that activeworkbook.worksheets(SortSheet) (where SortSheet is defined as "Output") is not recognized / does not exist. Could you please check wether the data you try to sort is indeed on a sheet named "Output"? If the sheet name differs, please adapt the definition of SortSheet accordingly in the code above...

 

[Chris Waller]

adkock,

Thanks for all your help. I did try replicating the spreadsheet at home last night, however, I did not remember to re-name the sheet, hence the reason why your code did not appear to work. As I like to keep my code generic, I wonder, if I have to reference the sheet name at all? I cannot say how much I appreciate your help on this. It could over the comming months save hours of work. Thanks again.

 

[adkock]

Happy to help!

Try replacing the line

SortSheet = "Output"

with

SortSheet = activesheet.name