I am having some trouble getting a range to sort. Interestingly enough, this was working until I tried to add some options to the Range.sort function (which I've since removed). The error code given is "Run-time error '1004': Sort method of Range class Failed". I've posted my code below, but I can't pinpoint why the sort is failing. I've looked at all the column and row values, and they are all correct. It's trying to sort "B2:B39" which looks fine on my sheet. I also attached a picture of the hidden variables sheet to show what the output looks like until the sort fails.
Edit: To clarify, I have tried copying, removing uniques, and then sorting in the first row as well, but I get the same error regardless. The last time the function was working, I believe the range did start from the second row.
Sub addUniques()
Dim i As Long
For i = 2 To 9
Call copyColToSheet(ThisWorkbook.Sheets("CallLog"), ThisWorkbook.Sheets("HiddenVariables"), NumToLet(i), NumToLet(i))
Next i
End Sub
Function copyColToSheet(wsSource As Worksheet, wsDest As Worksheet, colSource As String, colDest As String)
wsSource.Range(colSource & "2:" & colSource & CStr(getLastRowInCol(wsSource, colSource))).Copy wsDest.Range(colDest & "2:" & colDest & CStr(getLastRowInCol(wsDest, colDest)))
wsDest.Range(colDest & "2:" & colDest & CStr(getLastRowInCol(wsDest, colDest))).RemoveDuplicates Columns:=1, Header:=xlNo
wsDest.Range(colDest & "2:" & colDest & CStr(getLastRowInCol(wsDest, colDest))).Sort
End Function
Function getLastRowInCol(ws As Worksheet, col As String) As Long
With ws
getLastRowInCol = .Range(col & .Rows.Count).End(xlUp).Row
End With
End Function
Function NumToLet(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
NumToLet = vArr(0)
End Function
