1

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.

Output range I'm trying to sort

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

1 Answer 1

1

I think you need to set a Key

Try this:

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 Key1:=wsDest.Range(colDest & "2")
End Function
Sign up to request clarification or add additional context in comments.

1 Comment

Yep, that was it. Thank you for the help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.