VBA: Application-Defined Error when running LastRow Code

Question

I'm trying to get the last row of data after opening an excel Workbook but it's returning "Run Time Error 1004: Application-defined or object-defined error" I've used this format for code many times so I'm not sure what I'm missing here. I defined the variable as Long, does anyone have any ideas? My code is below:

Function get_end_row(ByVal column_with_data As Long) As Long
Dim last_row As Long

last_row = ThisWorkbook.Sheets(1).Rows.Count
get_end_row = ThisWorkbook.Sheets(1).Cells(last_row, column_with_data).End(xlUp).Row

End Function

Sub Master()
Call MVP
End Sub

Sub MVP()
Dim endRow As Long
Dim wb As Workbook, ws As Worksheet
Dim lastRow1 As Long

Set wb = ThisWorkbook
Set ws = ThisWorkbook.Sheets(1)
endRow = get_end_row(1)

Set mvpcomm = Workbooks.Open("File Path")
Set wsMVPComm = mvpcomm.Sheets("Combined")
lastRow1 = wsMVPComm.Range("A" & Rows.Count).End(xlUp).Row
wsMVPComm.Range("A2:AZ" & lastRow1).Copy Destination:=ws.Range("A" & endRow + 1)



End Sub

If anyone has any ideas I'd really appreciate it! Thank you.

What row does the debugger stop on (highlight) after the error pops up? In general, I've seen this error when VBA has trouble finding a particular range, either because a different workbook happens to be active, or a sheet name has changed. — Todd Page
– Todd Page, Commented Mar 21, 2016 at 13:56
The code stops on lastRow1 = wsMVPComm.Range("A" & Rows.Count).End(xlUp).Row — MCJNY1992
– MCJNY1992, Commented Mar 21, 2016 at 13:57
step through your code and see which line actually throws the error — user3598756
– user3598756, Commented Mar 21, 2016 at 13:57
Try this: wsMVPComm.Range("A" & wsMVPComm.Rows.Count).End(xlUp).Row — BruceWayne
– BruceWayne, Commented Mar 21, 2016 at 14:04
I suspect your workbooks are different formats. Use: lastRow1 = wsMVPComm.Range("A" & wsMVPComm.Rows.Count).End(xlUp).Row — Rory
– Rory, Commented Mar 21, 2016 at 14:04

user3598756 · Accepted Answer · 2016-03-21 14:29:17Z

3

most probably the workbook you opened as wsMVPComm is of an old excel format while ThisWorkbook is of a new one

try

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

instead of

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

you could make your function a little more generic and help you avoiding such problems like follows

Function get_ws_end_row(ws As Worksheet, column_with_data As Long) As Long
    get_end_row = ws.Cells(ws.Rows.Count, column_with_data).End(xlUp).Row
End Function

and then you could use it twice in the code you posted

endRow = get_ws_end_row(ws, 1)
...
lastRow1 = get_ws_end_row(wsMVPComm, 1)

edited Mar 21, 2016 at 14:29

answered Mar 21, 2016 at 14:04

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA: Application-Defined Error when running LastRow Code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related