VBA to sort with optional second range

[trf]

I'm trying to write a macro that will accept either one or two "sort by" arguments, such that it will sort a range by one field if only one argument is entered, or by two fields if two arguments were entered. Here is my code:

Sub SortTable(wsTheSheet As Worksheet, rngRange1 As Range, Optional rngRange2 As Range)
' Assumes we will sort the table with name <WorksheetName>_table
    Dim strSortRN As String  ' that's Sort Range Name
    strSortRN = wsTheSheet.Name & "_table"
    If IsMissing(rngRange2) Then
        wsTheSheet.Range(strSortRN).Sort Key1:=Range(rngRange1), Header:=xlYes
    Else
        wsTheSheet.Range(strSortRN).Sort Key1:=Range(rngRange1), Key2:=Range(rngRange2), Header:=xlYes
    End If       
End Sub

The problem is that IsMissing only returns information for a Variant, so IsMissing(rngRange2) is always False (i.e., is not meaningful). I have also tried setting the optional Range2 argument as a Variant, but of course it makes the Sort method crash, since Key2 needs to be a Range. How do I get around this?

Thanks so much -
Rob

 

[erik.van.geit]

Hi,

did you try

If rngRange2 Is Nothing Then

kind regards,
Erik

 

[trf]

Ah, that does the trick. Sorry, I had tried IsEmpty and IsMissing and looked for related functions with the VBA Help, but (Arg) Is Nothing didn't come up in the related functions.

Thanks!
Rob

 

[erik.van.geit]

you're welcome

although obvious when you see it, I agree this one is difficult to locate in the helpfiles starting from "range"
you will see an intresting example with NOTHING in the helpfiles when you goto "FIND"