With Worksheets("Sheet2")
iUniqueRows = UniqueRows(.Range( _
.Cells(iTLRow,iTLCol),.Cells(iBRRow,iBRCol)))
End with

Note the period (.) preceding Range, Cells and Cells. This qualifies all so
they are on the same sheet. If not, only range is referenced to
Worksheets("Sheet2"). the unqualified cells references refer to the
activesheet (if the code is in a general module) of to the sheet containing
the code (if the code is in a sheet module).

--
Regards,
Tom Ogilvy


"jrisch" wrote:

Hi, I have a UDF as follows:

Function UniqueRows(rng As Range) As Integer
Dim iRow As Long
Dim iRow2 As Long
Dim iCol As Integer
Dim iCount As Integer
Dim colStrings As New Collection
Dim strKey As String

For iRow = 1 To rng.Rows.Count
strKey = ""
For iCol = 1 To rng.Columns.Count
strKey = strKey & "~" & rng.Cells(iRow, iCol).Text
Next iCol
On Error Resume Next
colStrings.Add strKey, strKey
On Error GoTo 0
Next iRow
UniqueRows = colStrings.Count
End Function

It produces the expected result when used on a worksheet even if the
range referenced is on a different one. However, when trying to use it
in a subroutine in VBA, I don't know how to pass a range to the UDF if
the range is not on the active worksheet.

I'm using a statement such as :
iUniqueRows =
UniqueRows(Worksheets("Sheet2").Range(Cells(iTLRow ,iTLCol),Cells(iBRRow,iBRCol)))

I get an error meesage about an Application error.

Any advice appreciated.

Regards, John.