Not that this only works with simple ranges...

Sub test()
Dim rng As Range

Set rng = SubRange(Range("A1:A100"))
MsgBox rng.Address
Set rng = SubRange(Range("A1:A15"))
MsgBox rng.Address

End Sub


Public Function SubRange(ByVal Target As Range) As Range
Dim lng As Long

lng = 20
If Target.Count < 20 Then lng = Target.Count - 1

Set SubRange = Range(Target.Cells(Target.Count - lng),
Target.Cells(Target.Count))

End Function

--
HTH...

Jim Thomlinson


"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