Get smaller range from within another range using VBA

[Craig]

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 are:

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

 

[Gary''s Student]

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.

 

[Jim Thomlinson]

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