Scribd
Upload
3 views2 pages

Excel Unique Values Array Formula

The document provides a VBA function called UNIQUES that extracts unique values from a specified range in Excel and returns them as a multi-cell array. It utilizes a Collection to store unique values and handles errors during the addition process. Additionally, there is a function RetArray that demonstrates how to use the UNIQUES function with a predefined array.

Uploaded by

palharjeet
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
3 views2 pages

Excel Unique Values Array Formula

The document provides a VBA function called UNIQUES that extracts unique values from a specified range in Excel and returns them as a multi-cell array. It utilizes a Collection to store unique values and handles errors during the addition process. Additionally, there is a function RetArray that demonstrates how to use the UNIQUES function with a predefined array.

Uploaded by

palharjeet
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Function UNIQUES(rng As Range) As Variant()

'THIS IS MULTI CELL ARRAY FORMULA

'SELECT FEW CELLS

'WRITE FORMULA IN TOPMOST CELL

'THEN PRESS ctrl+sHIFT+ENTER

'UNIQUE VALUES APPEAR IN THE SELECTED CELLS

'AS THE FORMULA GETS COPIED TO ALL SELECTED CELLS

Dim list As New Collection

Dim Ulist() As Variant

On Error Resume Next

For Each Value In rng

[Link] CStr(Value), CStr(Value)

Next

On Error GoTo 0

[Link] [Link]

ReDim Ulist([Link] - 1, 0)

For i = 0 To [Link] - 1

Ulist(i, 0) = list(i + 1)

Next

UNIQUES = Ulist

End Function

Function RetArray() As Variant()

Dim arr() As Variant


ReDim arr(2, 0)

arr(0, 0) = "sdfd"

arr(1, 0) = "sdfd"

arr(2, 0) = 2

UNIQUES = arr

End Function

You might also like