Function that uses dynamic variables to sort a range
Function SortRange_SingleColumn(myWs As Long, mySortCol As Long, myEndRow As Variant, myEndCol As Variant)
Dim iWs As Long, iSortCol As Long, iEndRow As Long, iEndCol As Long
'OOOOOOOOOOOOOOOOOOOOOOOOO
'Convert Data Types
'OOOOOOOOOOOOOOOOOOOOOOOOO
iWs = myWs
iSortCol = mySortCol
iEndRow = myEndRow
iEndCol = myEndCol
'OOOOOOOOOOOOOOOOOOOOOOOOO
Worksheets(myWs).Sort.SortFields.Clear
'Set the definition of the column to be sorted (Does not include Header row)
Worksheets(myWs).Sort.SortFields.Add Key:=Range(Worksheets(myWs).Cells(2, iSortCol).Address, Worksheets(myWs).Cells(iEndRow, iSortCol).Address), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Worksheets(myWs).Sort
'Define the entire range to be sorted
.SetRange Range(Worksheets(myWs).Cells(1, 1).Address, Worksheets(myWs).Cells(iEndRow, iEndCol).Address)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Function
Dim iWs As Long, iSortCol As Long, iEndRow As Long, iEndCol As Long
'OOOOOOOOOOOOOOOOOOOOOOOOO
'Convert Data Types
'OOOOOOOOOOOOOOOOOOOOOOOOO
iWs = myWs
iSortCol = mySortCol
iEndRow = myEndRow
iEndCol = myEndCol
'OOOOOOOOOOOOOOOOOOOOOOOOO
Worksheets(myWs).Sort.SortFields.Clear
'Set the definition of the column to be sorted (Does not include Header row)
Worksheets(myWs).Sort.SortFields.Add Key:=Range(Worksheets(myWs).Cells(2, iSortCol).Address, Worksheets(myWs).Cells(iEndRow, iSortCol).Address), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Worksheets(myWs).Sort
'Define the entire range to be sorted
.SetRange Range(Worksheets(myWs).Cells(1, 1).Address, Worksheets(myWs).Cells(iEndRow, iEndCol).Address)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Function
Call the Function:
Sub CallSortFunction()
Dim Ws1 As Long, iRe1 As Long, iCe1 As Long, iColSort As Long
Ws1 = Worksheets("Sheet1").Index
iRe1 = 139242
iCe1 = 92
iColSort = Application.InputBox(Prompt:="What[Column] contains the SupplierSKU for this project?", Title:="Specify Column by Clicking on ANY Cell.", Default:=Cells(2, 66).Address, Type:=8).Column
Call SortRange_SingleColumn(Ws1, iColSort, iRe1, iCe1)
End Sub
Dim Ws1 As Long, iRe1 As Long, iCe1 As Long, iColSort As Long
Ws1 = Worksheets("Sheet1").Index
iRe1 = 139242
iCe1 = 92
iColSort = Application.InputBox(Prompt:="What[Column] contains the SupplierSKU for this project?", Title:="Specify Column by Clicking on ANY Cell.", Default:=Cells(2, 66).Address, Type:=8).Column
Call SortRange_SingleColumn(Ws1, iColSort, iRe1, iCe1)
End Sub