We can use .Count

sub servient()
Set r1 = Range("A1:Z100")
Set r2 = Example(r1.Address)
MsgBox (r2.Address)
MsgBox (r2.Count)
End Sub

Function Example(s As String) As Range
Set r = Range(s)
If r.Count < 20 Or r.Count = 20 Then
Set Example = r
Exit Function
End If

k = r.Count - 20
i = 1
For Each rr In r
If i k Then
If Example Is Nothing Then
Set Example = rr
Else
Set Example = Union(rr, Example)
End If
End If
i = i + 1
Next
End Function

We pass the address of the big range to Example. It loops thru the big
range and returns the last 20 cells as the little range.
--
Gary''s Student - gsnu200806


"Craig" wrote:

Hello,

I am new to VBA in Excel, although I have some experience. I am trying to
write a function that asks the user to input a range of cells. From that
range of cells, I want to extract the last 20 cells in the range (or all the
cells, if the range is smaller than 20 cells) and use that 20-cell subrange
to perform various calculations.

E.G.

Function Example (Input As Range) As Variant

Dim subrange as Range

??

End Function

My two questions a

1. Is there a property of the Range object that can tell you how many cells
are in it?

2. Once I know how many cells are in it, what is the best syntax to extract
the last 20 cells in Input (above) and assign that range to subrange (above)?
I would then use subrange to perform the necessary calculations.

Any help is greatly appreciated!

Thanks,
Craig