VBA to sort with optional second range

trf

Board Regular
Joined
Apr 17, 2002
Messages
62
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:

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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

trf

Board Regular
Joined
Apr 17, 2002
Messages
62
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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"
 

Forum statistics

Threads
1,136,266
Messages
5,674,728
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top