Last cell selection .End(xlup) using VBA in Excel 2003 'lists' - requires two .Select to get correct cell?

Question

Hi All! I'm a new convert from Experts-Exchange since I noticed they increased the 'Free Premium Services' points threshold.

It seems that Excel 2003 has issues with the End(xlup) command when using it in a worksheet that contains an Excel 'List'.. If I select a cell outside the 'list' boundary, and then try to select the last cell in the worksheet by using VBA, I have to call the .Select function twice to make sure I am getting the correct cell. If the original cell is inside the 'list' boundary then i only need one .Select. My hacked together solution is below, with two selects, as I can never be sure what cell may be selected on save. I include a version check at open to run different code in Excel 2007 (this code fails in 2007, where the .End(xlUp) command works properly).

Is there a more eloquent way to handle this? Thanks for any help!

.Range("A1").Select
.Cells(.Rows.Count, "A").End(xlUp).Select
.Cells(.Rows.Count, "A").End(xlUp).Select
'two .Selects needed to select correct cell in Excel 2003 list because original selection (A1) was not in list'

.Range("A1").Select
.Cells(.Rows.Count, "T").End(xlUp)(-2, 1).Select
.Cells(.Rows.Count, "T").End(xlUp)(-2, 1).Select
'two .Selects needed to select correct cell in Excel 2003 list because original selection (A1) was not in list'

.Cells(.Rows.Count, "T").End(xlUp)(-3, 1).Select
'only one select needed here because original selection above was within the list'

Answer 1

See how this does:

Sub Example()
    Dim rngLstCell As Excel.Range
    Set rngLstCell = GetLastCell(Excel.Worksheets("Sheet1"))
    MsgBox "The last cell is: " & rngLstCell.Address, vbInformation
End Sub

Public Function GetLastCell(ByVal ws As Excel.Worksheet) As Excel.Range
    Dim rngRtnVal As Excel.Range
    With ws.UsedRange
        Set rngRtnVal = .Find("*", .Cells(1, 1), , , xlByRows, xlPrevious)
        If rngRtnVal Is Nothing Then
            Set rngRtnVal = .Cells(1, 1)
        End If
    End With
    Set GetLastCell = rngRtnVal
End Function

Using find may seem weird at first but it ends up being the most reliable way due to some vagaries in the way empty cells are handled.

This may not be perfect if your data is non-normalized (jagged).