Excel macro to select a dynamic range to generate a pivot table

I want to automate generating an expense report using a pivot table.

I want to use expense data that is reported in a variable (dynamic) range that always starts in the same cell, but it ends depending on how many expense entries there are - always starts in A10, but the ending cell is variable (i.e. could be J64 one time or K89 another time.)

When I record a macro to select the range by starting in cell A10, holding shift+ctl+end, it always creates a static range instead of "finding" the dynamic end cell.  - like 'ExpenseDetail'!R10C1:R48C9"

Below is some code that I currently have.  How should this code be to find the changing address of the last cell in the data range and generates the pivot table using all of the expense data in that report?

Attached are 2 sample expense files with data that I want to run the macro on.  Each has a different number of expenses in it.

Sub a_format_iXpense_report()
'
' a_format_iXpense_report Macro
' Macro recorded 3/27/2010 
'

'
    ActiveSheet.Select
    ActiveSheet.Name = "ExpenseDetail"
    Range("A10").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range("H11").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Style = "Comma"
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "Me"
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A10").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'ExpenseDetail'!R10C1:R48C9").CreatePivotTable TableDestination:="", _
        TableName:="ExpenseSummary", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("ExpenseSummary").AddFields RowFields:="Date", _
        ColumnFields:="Category"
    ActiveSheet.PivotTables("ExpenseSummary").PivotFields("Amount").Orientation = _
        xlDataField
    ActiveCell.SpecialCells(xlLastCell).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
'    ActiveCell.Offset(-12, -8).Range("A1:I13").Select
    ActiveCell.Activate
    Selection.Style = "Comma"
    Selection.End(xlUp).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

Select allOpen in new window

example1.csv
example2.csv