Outlook VBA find last Row in Excel Worksheet

Question

I'm writing a function in Outlook VBA that involves reading content from an excel workbook.

The part I'm struggling with is finding the last row in a column (column A in this example). While the 1st line in the highlighted block correctly displays the content of A1 cell in given worksheet, the second line gives a Error "424" - object required.

Any suggestions into the problem would be greatly appreciated.

Public Function openExcel()



    Dim xlApp As Object
    Dim sourceWorkBook
    Dim sourceWorkSheet


    Dim cellVal As String
    Dim lastRow As Long


    Set xlApp = CreateObject("Excel.Application")

    With xlApp
        .Visible = True
        .EnableEvents = True
    End With

    Set sourceWorkBook = xlApp.Workbooks.Open("C:\SAMPLEPATH\Template.xlsx")
    Set sourceWorkSheet = sourceWorkBook.Worksheets("Sheet1")
    sourceWorkBook.Activate


    With Activesheet

        cellVal = sourceWorkSheet.Cells(1, 1)
        lastRow = sourceWorkSheet.Cells(.Rows.Count, "A").End(xlUp).Row

    End With



    sourceWorkBook.Save
    sourceWorkBook.Close
    xlApp.Quit



End Function

End(xlApp) - don't you mean End(xlUp)? (or maybe End(xlApp.xlUp)?) — YowE3K
I meant End(xlUp), Good Call. However, neither End(xlUp) or End(xlApp.xlUp) works. The prior results in "error 424 - Object Required" and the latter results in "error 438 - Object doesn't support this property or method" — z1lent
Do you have a reference to the Microsoft Excel library? If not, you will have to define the constant yourself then - i.e. End(-4162) — YowE3K

YowE3K YowE3K · Accepted Answer · 2017-06-08T01:54:19

If you want to have the ability to use Excel constants within your code, you will need to either

a) Include a reference to a Microsoft Excel Object Library, or

b) Create your own constant, e.g.

End(-4162)

or

Const xlUp As Long = -4162
...
... End(xlUp)

Outlook VBA find last Row in Excel Worksheet

1 Answers