Sure would oblige if one would kindly provide the CODE for developing a UDF
for simple formula of reversing the column values like the following:

=OFFSET($A$1,ROWS($A$1:$A$20)-ROWS($B$1:B1),0)

Thus, if a UDF like the following, is entered in an array form:
{=REVCOL(A1:A20)}
on the range B1:B20, the B1:B20 would represent the data of A1:A20 but in
the reverse order.

Thanx in advance,
Best Regards,

Faraz

I'm not sure why you would want a UDF to do what you already have a relatively compact formula doing, but here it is...

Function REVCOL(R As Range) As Variant
Dim CellValues As Variant, CallerRow As Long
CellValues = WorksheetFunction.Transpose(R)
CallerRow = Application.Caller.Row
REVCOL = CellValues(R.Count + R(1).Row - CallerRow)
End Function

Note that this is **not** an array formula... just commit it with the Enter key; and don't forget to make the range argument for it with absolute cell references so that when you copy the function down, the cell address doesn't "drift".

--
Rick (MVP - Excel)


"Faraz A. Qureshi" wrote in message ...
Sure would oblige if one would kindly provide the CODE for developing a UDF
for simple formula of reversing the column values like the following:

=OFFSET($A$1,ROWS($A$1:$A$20)-ROWS($B$1:B1),0)

Thus, if a UDF like the following, is entered in an array form:
{=REVCOL(A1:A20)}
on the range B1:B20, the B1:B20 would represent the data of A1:A20 but in
the reverse order.

Thanx in advance,
Best Regards,

Faraz

That's the main reason bro!
What I need is to have some knowledge of how to create an array formula?
Simply a construction model not necessarily for Reversing prpose.
It was only an example.
Please c if u can help me in giving some other example of your own as to how
to construct an array formula?

Thanx again!
Looking 4ward 4 your guidance.

Best Regards,

Faraz


"Rick Rothstein" wrote:

I'm not sure why you would want a UDF to do what you already have a relatively compact formula doing, but here it is...

Function REVCOL(R As Range) As Variant
Dim CellValues As Variant, CallerRow As Long
CellValues = WorksheetFunction.Transpose(R)
CallerRow = Application.Caller.Row
REVCOL = CellValues(R.Count + R(1).Row - CallerRow)
End Function

Note that this is **not** an array formula... just commit it with the Enter key; and don't forget to make the range argument for it with absolute cell references so that when you copy the function down, the cell address doesn't "drift".

--
Rick (MVP - Excel)


"Faraz A. Qureshi" wrote in message ...
Sure would oblige if one would kindly provide the CODE for developing a UDF
for simple formula of reversing the column values like the following:

=OFFSET($A$1,ROWS($A$1:$A$20)-ROWS($B$1:B1),0)

Thus, if a UDF like the following, is entered in an array form:
{=REVCOL(A1:A20)}
on the range B1:B20, the B1:B20 would represent the data of A1:A20 but in
the reverse order.

Thanx in advance,
Best Regards,

Faraz
.

On Jan 17, 9:45*am, Faraz A. Qureshi
wrote:
That's the main reason bro!
What I need is to have some knowledge of how to create an array formula?
Simply a construction model not necessarily for Reversing prpose.
It was only an example.
Please c if u can help me in giving some other example of your own as to how
to construct an array formula?

Thanx again!
Looking 4ward 4 your guidance.

Best Regards,

Faraz



"Rick Rothstein" wrote:
I'm not sure why you would want a UDF to do what you already have a relatively compact formula doing, but here it is...

Function REVCOL(R As Range) As Variant
* Dim CellValues As Variant, CallerRow As Long
* CellValues = WorksheetFunction.Transpose(R)
* CallerRow = Application.Caller.Row
* REVCOL = CellValues(R.Count + R(1).Row - CallerRow)
End Function

Note that this is **not** an array formula... just commit it with the Enter key; and don't forget to make the range argument for it with absolute cell references so that when you copy the function down, the cell address doesn't "drift".

--
Rick (MVP - Excel)

"Faraz A. Qureshi" wrote in ...
Sure would oblige if one would kindly provide the CODE for developing a UDF
for simple formula of reversing the column values like the following:

=OFFSET($A$1,ROWS($A$1:$A$20)-ROWS($B$1:B1),0)

Thus, if a UDF like the following, is entered in an array form:
{=REVCOL(A1:A20)}
on the range B1:B20, the B1:B20 would represent the data of A1:A20 but in
the reverse order.

Thanx in advance,
Best Regards,

Faraz
.- Hide quoted text -

- Show quoted text -

Here's a UDF using an array formula:

Function RevCol(ReverseA As Variant) As Variant
Dim NumRows As Long, TempA() As Double, i As Long

If TypeName(ReverseA) = "Range" Then ReverseA = ReverseA.Value2
NumRows = UBound(ReverseA)
ReDim TempA(1 To NumRows, 1 To 1)

For i = 1 To NumRows
TempA(i, 1) = ReverseA(NumRows - i + 1, 1)
Next i
RevCol = TempA

End Function

So the main things to remember a

Declare the range you are operating on and the function as variants
Convert the variant/range into an array with arrayname =
arrayname.value2 (the "If typename = "Range"" bit allows the function
to be used from another VBA routine, passing an array, rather than a
range).
Do what you want to the array
Assign the array at the functions return value.
Enter with Ctrl-Shift-Enter

This sort of array UDF will usually be much faster than a UDF working
on range objects.

These blog articles might help:
http://newtonexcelbach.wordpress.com...es-and-arrays/
http://newtonexcelbach.wordpress.com...-and-arrays-2/

By the way, for the non-VBA solution I'd use:
=INDEX($A$2:$A$21,ROW($A$21)-ROW(A1))
(for data in A2:A21)

Doug