Try this Function, it is storing unique values in Array Function GetUniqueList(rng As Range) As Variant > > On Error Resume Next > > Dim Arr() As Variant > Dim cell As Range > Dim r, c As Integer > Dim i, j As Integer > i = 0: j = 0 > > With Application.Caller > r = .Rows.Count > c = .Columns.Count > End With > ReDim Arr(r - 1, c - 1) > > For Each cell In rng > If WorksheetFunction.CountIf(rng.Cells(1, > 1).Resize(cell.Row, 1), > cell.Value) = 1 Then > Arr(i, j) = cell.Value > If j = c Then j = j + 1 > i = i + 1 > End If > > For k = i To UBound(Arr()) > Arr(k, 0) = "" > Next > Next > GetUniqueList = Arr > End Function >
-----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of hanumant shinde Sent: Thursday, August 11, 2011 10:28 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ UNIQUE values in Array Thanks but this will not work for me. i have already developed a function but i was thinking there must be some array function which will store just unique values in ARRAY but i guess now there's none. anyways thank for your help. --- On Tue, 9/8/11, dguillett1 <dguille...@gmail.com> wrote: > From: dguillett1 <dguille...@gmail.com> > Subject: Re: $$Excel-Macros$$ UNIQUE values in Array > To: excel-macros@googlegroups.com > Date: Tuesday, 9 August, 2011, 10:31 PM > try > > Sub GetUnique() > Application.ScreenUpdating = False > Dim lr As Long > lr = Cells(Rows.Count, 1).End(xlUp).Row > 'get unique > Cells(2, 1).Resize(lr).AdvancedFilter Action:=xlFilterCopy, > _ > CopyToRange:=Range("D2"), Unique:=True > 'sort > Cells(3, "d").Resize(lr).Sort Key1:=Range("D3"), _ > Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _ > MatchCase:=False, Orientation:=xlTopToBottom > Application.ScreenUpdating = True > End Sub > > -----Original Message----- From: Rajan_Verma > Sent: Tuesday, August 09, 2011 10:16 AM > To: excel-macros@googlegroups.com > Subject: RE: $$Excel-Macros$$ UNIQUE values in Array > > Hi, > its better to use advance filter Manually again and > again if we are > Increasing Our Range, > Suppose we need a List Validation of Unique Number From a > Range , we can use > this Function To Get Unique Values instantly , See > the attached File For > Example : > > > Manual Work in Excel is not in our scope if we have VBA. > > Thanks > Rajan Verma > > > -----Original Message----- > From: excel-macros@googlegroups.com > [mailto:excel-macros@googlegroups.com] > On Behalf Of dguillett1 > Sent: Tuesday, August 09, 2011 8:31 PM > To: excel-macros@googlegroups.com > Subject: Re: $$Excel-Macros$$ UNIQUE values in Array > > Will data>advanced filter>unique work for you? > > -----Original Message----- From: Rajan_Verma > Sent: Tuesday, August 09, 2011 9:04 AM > To: excel-macros@googlegroups.com > Subject: RE: $$Excel-Macros$$ UNIQUE values in Array > > Hi, > I was trying to Get Unique List by Using Array Function and > the Output is > below , it's a array Function To Get Unique values List > From Given Range > > > Function GetUniqueList(rng As Range) As Variant > > On Error Resume Next > > Dim Arr() As Variant > Dim cell As Range > Dim r, c As Integer > Dim i, j As Integer > i = 0: j = 0 > > With Application.Caller > r = .Rows.Count > c = .Columns.Count > End With > ReDim Arr(r - 1, c - 1) > > For Each cell In rng > If WorksheetFunction.CountIf(rng.Cells(1, > 1).Resize(cell.Row, 1), > cell.Value) = 1 Then > Arr(i, j) = cell.Value > If j = c Then j = j + 1 > i = i + 1 > End If > > For k = i To UBound(Arr()) > Arr(k, 0) = "" > Next > Next > GetUniqueList = Arr > End Function > > > -----Original Message----- > From: Rajan_Verma [mailto:rajanverma1...@gmail.com] > Sent: Saturday, August 06, 2011 9:59 AM > To: 'excel-macros@googlegroups.com' > Subject: RE: $$Excel-Macros$$ UNIQUE values in Array > > Hope this will Help You. > > Function UniqueList(rng As Range, Pos As Long) As String > Dim List() As String > Dim cell As Range > Dim i As Long > Dim t As Long > i = 0 > ReDim List(rng.Cells.Count) As String > For Each cell In rng > flag = 0 > > For t = LBound(List) To UBound(List) > > If cell.Value = List(t) Then > > > flag = 1 > > Exit For > > End If > > Next > > > If flag = 0 > Then > > List(i) = > cell.Value > > i = i + 1 > > End If > Next > UniqueList = List(Pos) > End Function > > > > -----Original Message----- > From: excel-macros@googlegroups.com > [mailto:excel-macros@googlegroups.com] > On Behalf Of hanumant shinde > Sent: Saturday, August 06, 2011 2:20 AM > To: Excel Group > Subject: $$Excel-Macros$$ UNIQUE values in Array > > Hi friends, > > i have some values in column A. i want to take only UNIQUE > values in some > array. > how can i do so? > > i have developed below function and is working exactly i > want it to be but i > > think there should be more efficient way of doing so like > there may be array > > function for storing only UNIQUE values or anything like > that. > > Sub UniqueArray() > > Dim newarr() As String > Dim blnmatchfnd As Boolean > j = 0 > For i = 1 To 46 > ReDim Preserve newarr(j) > For k = 0 To UBound(newarr) > If newarr(k) = Range("A" & i).Value Then > blnmatchfnd = True > Exit For > Else > blnmatchfnd = False > End If > Next k > > If blnmatchfnd = False Then > ReDim Preserve newarr(j) > newarr(j) = Range("A" & i).Value > Range("B" & j + 1).Value = newarr(j) > j = j + 1 > End If > > Next i > End Sub > > -- > ---------------------------------------------------------------------------- > ------ > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > > -- > ---------------------------------------------------------------------------- > ------ > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > > -- > ---------------------------------------------------------------------------- > ------ > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > > -- > ---------------------------------------------------------------------------- ------ > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- > ---------------------------------------------------------------------------- ------ > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- ---------------------------------------------------------------------------- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
