vba - last row in column

[Kaps_mr2]

Dear All,

I am trying to find the last non blank cell in a column. I have the following code - but get an application 1004 error on the line in bold.

the msgbox returns a valid address. "graph" is a valid sheet name and price_workbook exists.Any thoughts ? thanks

regards

Kaps

Public Function last_non_blank_cell(cell_address As Range) As Long
MsgBox cell_address.Address
    last_non_blank_cell = price_workbook.Sheets("graph").Range(cell_address).End(xlDown).Row
End Function

 

[doofusboy]

Here's one way: (finds last non-blank cell in column D)

Dim lngLastRow As Long
    lngLastRow = wsExperiments.Range("D65536").End(xlUp).Row

 

[kpark91]

It seems like your code wants to find next blank column since it does End(xlDown).
Keep in mind that End(direction) simulates the same action as pressing ctrl+direction in the spreadsheet.

Anyways, here is the code that may/may not help:

Public Function last_non_blank_cell(cell_address As Range) As Long
MsgBox cell_address.Address
    last_non_blank_cell = price_workbook.Sheets("graph").Cells(Rows.count,cell_address.column).End(xlUp).Row
End Function

 

[Kaps_mr2]

Thanks Kpark91 - that stopped my error (no idea why it caused it though).

 

[Jonmo1]

Thanks Kpark91 - that stopped my error (no idea why it caused it though).

Because Range(...) expects a STRING like "A1"
But since cell_address is dimmed as a Range,
you fed it another range object.

For your original code to work you would want

last_non_blank_cell = price_workbook.Sheets("graph").Range(cell_address.Address).End(xlDown).Row

 

[Kaps_mr2]

thank you jonmo1 !!! as ever stumped by the obvious !!!