 # Last Cell Computation ok in Sub but Not in Function

• Hi all,

I've written a UDF to sum up all the bold numbers in a 1-column range. The initial code worked fine, except that if the specified range was an entire column then the computation took some time to process all 65536 rows. I added the condition that only those cells should be processed whose row is less than or equal to the row of the last used cell. For this I added the variable LastRow. Although the formula for computing LastRow works correctly in a Sub procedure, the value returned within the Function, for reasons best known to Mr. Gates, is always 1.

Would appreciate any help with this. My code is reproduced below.

Thanks

m

• Re: Last Cell Computation ok in Sub but Not in Function

Hi,

Interesting the same syntax when called from a worksheet cell gives a different result. In the cell =Test() returns \$A:\$A while the subroutine returns, in my example, \$A\$25 which is the last cell.[vba]Function Test() As String

End Function

Sub X()

MsgBox Test

End Sub[/vba]

This mod should fix your UDF[vba]Function SUMBOLD(rngSumRange As Range) As Variant
'LastRow returns erroneous result in function.

Dim rCell As Range
Dim LastRow As Long

If rngSumRange.Columns.Count > 1 Then SUMBOLD = "NUM!": Exit Function

LastRow = rngSumRange.Cells(rngSumRange.Cells.Count, 1).End(xlUp).Row
SUMBOLD = 0
For Each rCell In rngSumRange
If rCell.Row > LastRow Then Exit Function
If IsNumeric(rCell) And rCell.Font.Bold = True Then
SUMBOLD = SUMBOLD + rCell
End If
Next

End Function[/vba]

[h4]Cheers
Andy
[/h4]

• Re: Last Cell Computation ok in Sub but Not in Function

Thanks Andy. Works great!

m

## Participate now!

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