VBA End(xlUp) ignore blank

[EOAEvan]

Im using the line .End(xlUp) to test for the last used cell. However I have forumlas the have the result of "". I want to test for the last used row that is greater than 0 and ignore the cells that have the formula with the result of "".

Thanks

 

[tigeravatar]

EOAEvan,

Assuming column A, you can use this. If you have more than 65000 rows, just adjust the range.

    Dim LastUsedRow As Long
    LastUsedRow = [LOOKUP(2,1/(A1:A65000<>""),ROW(A1:A65000))]

 

[taurean]

Maybe like this:

Dim lLR As Long
lLR = Sheets(1).Cells.Find("*", Cells(Rows.Count, Columns.Count), xlValues, xlPart, xlByRows, xlPrevious).Row

 

[EOAEvan]

EOAEvan,

Assuming column A, you can use this. If you have more than 65000 rows, just adjust the range.

    Dim LastUsedRow As Long
    LastUsedRow = [LOOKUP(2,1/(A1:A65000<>""),ROW(A1:A65000))]

This works. Thank you

 

[EOAEvan]

Maybe like this:

Dim lLR As Long
lLR = Sheets(1).Cells.Find("*", Cells(Rows.Count, Columns.Count), xlValues, xlPart, xlByRows, xlPrevious).Row

I should have been more clear in my original post. I need to find the last cell in a specific row rather than the whole sheet.

 

[pgc01]

Hi

Another option, based on Shrivallabha's solution, for a specific range, for ex. Column A:

Dim lLR As Long
lLR = Sheets(1).Columns("A").Find("*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row