Excel Control+End goes to bottom of Formatted rather than end of data VBA fix

[Rx_]

Just wondering if anyone knows of a shortcut or shorter solution to this issue?
Problem: Using a Template or existing Excel Workbook with headers, all the formatting ready to go.
Then use MSAccess code (set ObjXL as Excel.Application) to automate custom data and use CopyFromRecordset to populate the data under the Excel headers.
Using the RecordCount, add a Total row with formulas for the custom Totals.

Excel has a feature to goto the last data row (Control+End).
Problem: the Control+End goes to the last cell row that is Formatted OR that has data.
The "template" has all the columns custom formatted down to row 50,000.
The data may be 20 rows or 20,000 rows.. So, the Control+End way overshoots the final data row.


Looked at several code segments, not all work the same.
This code is designed to run from MSAccess (where ObjXL is a reference and Object Variable set to Excel.Application.

Is there a shorter method to achieve the same result?
The Access project automation has loops and is producing around 300 worksheets every morning. Any streamline would be worth considering.

Sub DeleteUnusedFormats() ' Resets Used Range so Control+End doesn't go to last formated cell in Template
    Dim lLastRow As Long, lLastColumn As Long
    Dim lRealLastRow As Long, lRealLastColumn As Long
    With ObjXL.Range("A6").SpecialCells(xlCellTypeLastCell) ' A6 is row used to copyfromrecordset for data under Header row in template
        lLastRow = .Row  ' Found last Formatted row
        lLastColumn = .Column
    End With
    lRealLastRow = ObjXL.Cells.Find("*", ObjXL.Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
    lRealLastColumn = ObjXL.Cells.Find("*", ObjXL.Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
    If lRealLastRow < lLastRow Then
        ObjXL.Range(ObjXL.Cells(lRealLastRow + 1, 1), ObjXL.Cells(lLastRow, 1)).EntireRow.Delete
    End If
    If lRealLastColumn < lLastColumn Then
        ObjXL.Range(ObjXL.Cells(1, lRealLastColumn + 1), ObjXL.Cells(1, lLastColumn)).EntireColumn.Delete
    End If
    ObjXL.ActiveSheet.UsedRange 'Resets LastCell to 1 below last row - in this cast below a programmed TOTAL row
End Sub

Plus: this code works, was just pleased to have it working.

 

[Gasman]

I've only ever used these in Excel, but cannot see why they would not work on an Excel object?

iLastRow = ActiveSheet.UsedRange.Count

lLastRow = Range("A" & Rows.Count).End(xlUp).Row

 

[Rx_]

That worked perfect on a workbook in a macro.
Added many rows of Formatting below the last row with data.

Will have to try this in other code such as Sorting and Grouping.
Thanks for the tip!