VBA Error "Object does not support this property or method"

[Henry1]

hello everyone:

I am a certified excel expert but kind of new to VBA world. I have a simple VBA code but keep getting the error message "Object ......"

Sub test()

ThisWorkbook.Sheets("Henry").Cells(Rows.Count, 1).End(xlUp).Row

End Sub



I have an excel workbook with a worksheet name called Henry. when I run the code why do I get the error message?

thanks for help!

Henry

 

[Max1616]

Hello! This is occurring because you are not telling the code to actually do something. Essentially you are stating an object (The largest row in column 1 with data) but you aren't telling excel to do anything with it. For example, let's say you would like to make column B for that row contain the word "TEST":

Cells([B]ThisWorkbook.Sheets("Henry").Cells(Rows.Count, 1).End(xlUp).Row[/B], 2) = "TEST"

Another thing you can do is assign that value to a variable and then use it in the future:

Sub Henry()
Dim LastRow As Long

LastRow = ThisWorkbook.Sheets("Henry").Cells(Rows.Count, 1).End(xlUp).Row

Cells(LastRow, 2) = "Test"

End Sub

I hope this help! If you provide an example of what specifically you are trying to do with that code, we can help you further.

 

[Trebor76]

Hi Henry,

Welcome to MrExcel!!

You need to assign a variable to the code you're using to find the last row in Col. A or just display it in a message box. Assuming you want to do something with row number that the code finds try this:

Option Explicit
Sub test()

    Dim lngLastRow As Long

    lngLastRow = ThisWorkbook.Sheets("Henry").Cells(Rows.Count, 1).End(xlUp).Row
    
    MsgBox lngLastRow

End Sub

Regards,

Robert