Preventing a sort function from failing when no text is available to sort

Question

So I'm having a couple issues with the code provided below. The first issue that I'm having is the fact that if I am in "Sheet1" and run the code I get a run time error "Select method of Range class failed." Running the code from "Sheet2" works fine. I'm fairly new to VBA and from my understanding I've defined the sheets to select from so I'm not sure where the error is coming from.

The second problem I'm having is with the sort function. If the source selection doesn't have any text the sort function fails because there's nothing to sort (makes sense). I just don't know how to add a function to sort the selection if there is text, and just pass it if it doesn't.

I know the code I provided is probably pretty mediocre and there are probably better ways of handling the same set of data. I welcome any suggestions to streamline and manage this code better.

Sub DaysCalc()
'Day 1
    ToColumn Sheets("Sheet1").Range("B4:AD22"), _
                         Sheets("Sheet2").Range("A2")

    Sheets("Sheet2").Range("A2:A552").Select
    Selection.sort Key1:=Sheets("Sheet2").Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

    Application.DisplayAlerts = False

    Sheets("Sheet2").Range("A2:A552").Select
    Selection.TextToColumns Destination:=Sheets("Sheet2").Range("B2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
    Array(7, 1))


End Sub


Sub ToColumn(rngIn As Range, topCell As Range)

    Dim rv() As Variant, n As Long, d, r As Long, c As Long
    Dim nR As Long, nC As Long, i As Long

    d = rngIn.Value
    nR = UBound(d, 1)
    nC = UBound(d, 2)
    n = nR * nC
    ReDim rv(1 To n, 1 To 1)
    i = 0
    For r = 1 To nR
    For c = 1 To nC
        i = i + 1
        rv(i, 1) = d(r, c)
    Next c
    Next r

    topCell.Resize(n, 1).Value = rv
End Sub

Here's a great write-up on how to avoid using `.select`: http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select — Dan Wagner, May 06 '14 at 17:52

score 0 · Accepted Answer · answered May 06 '14 at 17:50

The error "Select method of Range class failed." when running this on Sheet1 is because of the line Sheets("Sheet2").Range("A2:A552").Select which is trying to select a range which is not on the Active Worksheet. You do not need to actually select the area to do the sort and can use Sheets("Sheet2").Range("A2:A552").Sort Key1:=Sheets("Sheet2").Range("A2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

I do not receive an error when trying to sort on an empty range, however, I am receiving it when I try to use the Text to Columns function. I would suggest adding On Error Resume Next immediately before it to avoid the issue.

Preventing a sort function from failing when no text is available to sort

1 Answers1