Using UDF Returned Value in Worksheet Function

Question

I have created a UDF that returns the last populated row in a specific column. Right now, it returns the row number of the last populated row. My question is, how would I go about using this value in a worksheet function?

For example, =COUNTBLANK("B2:B&LastRow(2)), where LastRow(2) returns an integer.

score 0 · Accepted Answer · answered Oct 30 '14 at 21:15

0

First the UDF

Public Function LastRow(Rin As Range) As Long
    cl = Rin.Column
    LastRow = Cells(Rows.Count, cl).End(xlUp).Row
End Function

and then to use it in the worksheet:

=COUNTBLANK(INDIRECT("B2:B"& LastRow(B:B)))

answered Oct 30 '14 at 21:15

Gary's Student

95,722
10
59
99

score 0 · Answer 2 · answered Oct 30 '14 at 21:35

Why use VBA when you can achieve what you want with formulas?

To get the last row in a column, use this formula.

=MATCH(9.9E+307,A:A,1)

The above will give you the last row of a column A

To use it with your formula try this.

=COUNTBLANK(INDIRECT("B2:B"& MATCH(9.9E+307,B:B,1)))

Using UDF Returned Value in Worksheet Function

2 Answers2