VBA Code addition for second column to sort on

A

alan.sluder

New member
Joined
Nov 11, 2014
Messages
8
Reaction score
0
Points
0
Excel Version(s)
Excel 2013, 2016
Having trouble remembering how to add an additional column to sort on in a worksheet.

Below is the current code that sorts column "C". Need to add the ability sort on column "C" first then sort column "B".

Sub SortAllSheets()
'this sorts each sheet in same fashion
'assumes row 1 has labels
'
'redefine these to suit your requirements
Const firstColToSort = "A"
Const lastColToSort = "F"
Const keyCol = "C" ' field to sort on
'this next should be a column that will
'always have entries in it, and can be
'same as keyCol but does not have to be.
Const testCol = "C"


Dim anyWS As Worksheet
Dim sortRange As Range
Dim sortKey As Range
'next improves performance
Application.ScreenUpdating = False
For Each anyWS In ThisWorkbook.Worksheets
Set sortRange = anyWS.Range(firstColToSort & "4:" _
& lastColToSort & _
anyWS.Range(testCol & Rows.Count).End(xlUp).Row)
Set sortKey = anyWS.Range(keyCol & 2)
sortRange.Sort Key1:=sortKey, Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Next
'just good housekeeping
Set sortRange = Nothing
Set sortKey = Nothing
Set anyWS = Nothing
End Sub
 
Code: 
Sub SortAllSheets()'this sorts each sheet in same fashion
'assumes row 1 has labels
'
'redefine these to suit your requirements
Const firstColToSort = "A"
Const lastColToSort = "F"
Const keyCol = "C" ' field to sort on
'this next should be a column that will
'always have entries in it, and can be
'same as keyCol but does not have to be.
Const testCol = "C"

Dim anyWS As Worksheet
Dim sortRange As Range
Dim sortKey1 As Range
Dim sortKey2 As Range


    'next improves performance
    Application.ScreenUpdating = False


    For Each anyWS In ThisWorkbook.Worksheets


        Set sortRange = anyWS.Range(firstColToSort & "4:" & _
                        lastColToSort & _
                        anyWS.Range(testCol & Rows.Count).End(xlUp).Row)
        Set sortKey1 = anyWS.Range(keyCol & 2)
        Set sortKey2 = anyWS.Range(keyCol & 3)
        sortRange.Sort Key1:=sortKey, _
                       Order1:=xlAscending, _
                       Key2:=sortKey, _
                       Order2:=xlAscending, _
                       Header:=xlYes, _
                       OrderCustom:=1, _
                       MatchCase:=False, _
                       Orientation:=xlTopToBottom
    Next
    
    'just good housekeeping
    Set sortRange = Nothing
    Set sortKey = Nothing
    Set anyWS = Nothing
End Sub
 
getting run-time error that the sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort box isn't the same or blank.

this is what i was running into when i tried adding the second column to sort on. Works fine when i just sort on only the one column,.
 
Thanks Bob for the help. You jogged my memory and i was able to fix my coding to the below and get it work correctly.

Code: 
Sub SortAllSheets() 'this sorts each sheet in same fashion
'assumes row 1 has labels
'
'redefine these to suit your requirements
Const firstColToSort = "A"
Const lastColToSort = "F"
Const keyCol = "C" ' field to sort on
'this next should be a column that will
'always have entries in it, and can be
'same as keyCol but does not have to be.
Const keyCol2 = "B"
Const testCol = "C"


Dim anyWS As Worksheet
Dim sortRange As Range
Dim sortKey1 As Range
Dim sortKey2 As Range


	'next improves performance
	Application.ScreenUpdating = False


	For Each anyWS In ThisWorkbook.Worksheets


		Set sortRange = anyWS.Range(firstColToSort & "4:" & _
		lastColToSort & _
		anyWS.Range(testCol & Rows.Count).End(xlUp).Row)
		Set sortKey1 = anyWS.Range(keyCol & 2)
		Set sortKey2 = anyWS.Range(keyCol2 & 2)
		sortRange.Sort Key1:=sortKey1, _
			Order1:=xlAscending, _
			Key2:=sortKey2, _
			Order2:=xlAscending, _
			Header:=xlYes, _
			OrderCustom:=1, _
			MatchCase:=False, _
			Orientation:=xlTopToBottom
	Next


	'just good housekeeping
	Set sortRange = Nothing
	Set sortKey = Nothing
	Set anyWS = Nothing
End Sub
 
Last edited by a moderator:
Sorry about that, silly mistake on my part not changing Sortkey to Sortkey1/2.
 
You must log in or register to reply here.
Back
Top