How to find the Dimensions of a Variant Range
 

I have a worksheet contains a named range referenced to cells A1:C3 called
InputRange
I read this range into a variant and then want the dimensions of the
variant.

Sub ReadRange()
Dim IRange() As Variant
IRange() = Range("InputRange")
End Sub

I tried but I can only find the vertical dimension with the following:
MsgBox UBound(IRange)
Can someone help me how to find the other dimension?

Thanks a lot on your ideas...

Frans

How to find the Dimensions of a Variant Range
 

Try

Function GetDims(Arr() As Variant) As Long
Dim Ndx As Long: Ndx = 1
Dim SaveNdx As Long
Dim V As Variant
On Error Resume Next
Do Until Err.Number < 0
V = UBound(Arr, Ndx)
If Err.Number = 0 Then
SaveNdx = Ndx
Else
GetDims = SaveNdx
End If
Ndx = Ndx + 1
Loop
End Function

You can then call this as

Dim Dims As Long
Dims = GetDims(IRange)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Frans Verhaar" wrote in message
...
I have a worksheet contains a named range referenced to cells
A1:C3 called InputRange
I read this range into a variant and then want the dimensions
of the variant.

Sub ReadRange()
Dim IRange() As Variant
IRange() = Range("InputRange")
End Sub

I tried but I can only find the vertical dimension with the
following: MsgBox UBound(IRange)
Can someone help me how to find the other dimension?

Thanks a lot on your ideas...

Frans

How to find the Dimensions of a Variant Range
 

You should include the LBound as well

MsgBox UBound(IRange, 1) - LBound(IRange,1) + 1

MsgBox UBound(IRange, 2) - LBound(IRange,2) + 1

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Frans Verhaar" wrote in message
...
I have a worksheet contains a named range referenced to cells A1:C3 called
InputRange
I read this range into a variant and then want the dimensions of the
variant.

Sub ReadRange()
Dim IRange() As Variant
IRange() = Range("InputRange")
End Sub

I tried but I can only find the vertical dimension with the following:
MsgBox UBound(IRange)
Can someone help me how to find the other dimension?

Thanks a lot on your ideas...

Frans

How to find the Dimensions of a Variant Range
 

Dim lb1&, lb2&, ub1&, ub2&, IRange
lb1 = lbound(IRange,1)
ub1 = Ubound(IRange,1)
lb2 = lbound(IRange,2)
ub2 = Ubound(IRange,2)

--
Regards,
Tom Ogilvy


"Frans Verhaar" wrote:

I have a worksheet contains a named range referenced to cells A1:C3 called
InputRange
I read this range into a variant and then want the dimensions of the
variant.

Sub ReadRange()
Dim IRange() As Variant
IRange() = Range("InputRange")
End Sub

I tried but I can only find the vertical dimension with the following:
MsgBox UBound(IRange)
Can someone help me how to find the other dimension?

Thanks a lot on your ideas...

Frans

How to find the Dimensions of a Variant Range
 

Hi,
That works very well. It's quite slim and fast.
Thanks to all of you.

Regards,
Frans

"Bob Phillips" wrote in message
...
You should include the LBound as well

MsgBox UBound(IRange, 1) - LBound(IRange,1) + 1

MsgBox UBound(IRange, 2) - LBound(IRange,2) + 1

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Frans Verhaar" wrote in message
...
I have a worksheet contains a named range referenced to cells A1:C3
called
InputRange
I read this range into a variant and then want the dimensions of the
variant.

Sub ReadRange()
Dim IRange() As Variant
IRange() = Range("InputRange")
End Sub

I tried but I can only find the vertical dimension with the following:
MsgBox UBound(IRange)
Can someone help me how to find the other dimension?

Thanks a lot on your ideas...

Frans