Get smaller range from within another range using VBA
 

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

Get smaller range from within another range using VBA
 

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

Get smaller range from within another range using VBA
 

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