Quick, Easy Excel Question, Need to select full active range of worksheet

Dear Experts,

I wrote a function I kinda like which is pretty neat, but I was doing a bit of testing on it and I realized it has a flaw..

For example if you have an excel sheet something like:

--------------------
| a | b | c  |
--------------------
|    |    | v |
--------------------
|    |    |    |
-------------------
| v |    |    |
--------------------
|    | v |    |
--------------------

There is a line in my code whose point is to go to the bottom-most cell and then the right-most cell.

'Select the range from the top-left corner of the worksheet to the bottom right corner of the worksheet
    Set rgTopCell = aSheet.Cells(iHeaderRow + 1, 1)  'start right below the header so it stays at the top
    Set rgSheet = aSheet.Range(rgTopCell, rgTopCell.End(xlDown).End(xlToRight))

Select allOpen in new window

But what it's doing when I run it is instead of selecting A-C, 1-4, it is just selecting the first three rows. What I want it to do is give me a range from the row right below the header row (if there is one) all the way to the bottom-right corner of the last column that has a value and the last row that has a value.

Because the point is to select the entire sheet (expand selection) and sort it by a column or two depending on how the function is called.

I think there may even be a property that just selects the active area of the sheet, but I can't seem to find it..

Anyone know the best way to make sure that all the rows and columns get included in the sort without selecting every single possible row and column on the worksheet? What I'm trying to avoid here is using the range columns "A" to "IV" and rows 1 to 65536, but actually just select the entire range which includes data, whether or not there are blank rows or columns in between the data.

Thanks, ~Jeffrey

This is code to call it and reproduce the error

Public Sub Test_SAC()
    Const cTestPath$ = "C:\Documents and Settings\Michael\Desktop\test.xls"
    Dim anApp As New Excel.Application, aBook As Excel.Workbook, aSheet As Excel.Worksheet
    anApp.Visible = True
    Set aBook = anApp.Workbooks.Open(cTestPath)
    Set aSheet = aBook.Sheets(1)
    SortAColumn aSheet, "A", True
    Set anApp = Nothing
    Set aBook = Nothing
    Set aSheet = Nothing
End Sub

Select allOpen in new window

Here is the code for the function in question:

Public Function SortAColumn(ByRef aSheet As Excel.Worksheet, vSortCol As Variant, bAscending As Boolean, _
    Optional iHeaderRow As Integer = 0, Optional vSortCol_2 As Variant = 0, Optional bAscending_Col2 As Boolean = True)
    'Written by Jeffrey Durham 2/8/2012
    'This function will sort your excel sheet by one or two columns in ascending/descending order
    '  You may specify the header row or else Excel will guess, and the sort column can be a number or letter
    Dim rgTopCell As Excel.Range, rgKey As Excel.Range, rgKey_2 As Excel.Range, b2ndKey As Boolean
    Dim rgSheet As Excel.Range
    
    'Figure out which cells we're going to start sorting each column on
    b2ndKey = (CStr(vSortCol_2) <> "" And CStr(vSortCol_2) <> "0")
    Set rgKey = aSheet.Cells(iHeaderRow + 1, vSortCol)
    If b2ndKey = True Then Set rgKey_2 = aSheet.Cells(iHeaderRow + 1, vSortCol_2)
    
    'Select the range from the top-left corner of the worksheet to the bottom right corner of the worksheet
    Set rgTopCell = aSheet.Cells(iHeaderRow + 1, 1)  'start right below the header so it stays at the top
    Set rgSheet = aSheet.Range(rgTopCell, rgTopCell.End(xlDown).End(<wbr ></wbr><wbr ></wbr>xlToRight)<wbr ></wbr>)
    rgSheet.Select  '<-- this line is not necessary, it just shows you the selected range
    stop  '<-- here you can see how it's not selecting the right range

    'Apply the sort with either one or two keys
    If b2ndKey = False Then
        rgSheet.Sort rgKey, IIf(bAscending = True, xlAscending, xlDescending), , , , , , _
            xlGuess, 1, False, xlTopToBottom
    Else
        rgSheet.Sort rgKey, IIf(bAscending = True, xlAscending, xlDescending), rgKey_2, , _
            IIf(bAscending_Col2 = True, xlAscending, xlDescending), , , xlGuess, 1, False, xlTopToBottom
    End If
End Function

Select allOpen in new window