Excel Control+End goes to bottom of Formatted rather than end of data VBA fix (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 17:43
Joined
Oct 22, 2009
Messages
2,803
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.

Code:
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.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 23:43
Joined
Sep 21, 2011
Messages
14,048
I've only ever used these in Excel, but cannot see why they would not work on an Excel object?

Code:
iLastRow = ActiveSheet.UsedRange.Count

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

Rx_

Nothing In Moderation
Local time
Today, 17:43
Joined
Oct 22, 2009
Messages
2,803
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!
 

Users who are viewing this thread

Top Bottom