UDF - Find last used column within row

  • Hi,

    I am trying to create a function that would find the last column within a row and return its value.

    I have the following code :

    Function LASTCELL(oRng As Range) As Variant
    LASTCELL = oRng.Find(What:="*", LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False _
    , SearchFormat:=False)
    End Function

    It is working but for some reason, when I run a macro that use the result of this function to make a calculation, this function return !#VALUE. If I run application.calculate or I press enter in the cell, the value returned is correct.

    Is there another way to write this function. I think the fact that I use a Range class's Find method makes the UDF fail.

    Thank you for your help!

  • Re: UDF - Find last used column within row

    You could try this instead:

    Function LastInRow(MyRow As Long)
        LastInRow = Cells(MyRow, Columns.Count).End(xlToLeft).Column
    End Function

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!