unique records of a column

[G2K]

Hi all,

is there any way to get unique records from a specific column in Ms excel by formula or VBA.

i need to check unique records from specefic column and based on that criteria i want to paste all data from master sheet to other sheet.

i have written a code to do the this but it works on static definition

i want to change it dynamic

sub uniquerecords()
Dim ClientName(), i As Integer
ClientName() = Array("Abc", "def", "GHI", "JKL")

For i = 0 To UBound(ClientName)
Sheets("sheet1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=ClientName(i)
Cells.Copy
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = ClientName(i)
Sheets("sheet1").Select
Next i

end sub

how can i change it to dynamic...............

 

[DiscoPistol]

This will take the Uniques from range 'Data' and put them on another sheet (although you could put them anywhere really)

sub UniqueCollection()
Dim Uniques As New Collection
Set ReqRange = Range("Data")
On Error Resume Next
 
For Each Cell In ReqRange
Uniques.Add Cell.Value, CStr(Cell.Value)
 
Next Cell
 
Sheets("Sheet2").Range("b1").Select
For Each Item In Uniques
 
ActiveCell.Value = Item
ActiveCell.Offset(1, 0).Select
 
Next Item
 
End Sub

DP

 

[G2K]

Array in VBA

thanks.

Unfortunately it's not working ....

i am using array first time, please check the below code and and suggest wht's going wrong or pls provide correct code to fix this problem


Function StoreUniqueRecordsInArray() As String()
Dim strNames() As String
Dim lElement As Long
Dim Uniques As New Collection
Dim ReqRange As Range
Set ReqRange = Range("B2:B10000")
rcount = 0
On Error Resume Next
For Each cell In ReqRange
Uniques.Add cell.Value, CStr(cell.Value)
Next cell
For Each Item In Uniques
rcount = rcount + 1
strNames(rcount) = Item

Next Item
StoreUniqueRecordsInArray = strNames
End Function


Private Sub test()
Dim i As Integer
Dim ClientName() As String
ClientName = StoreUniqueRecordsInArray
For i = 1 To rcount

Sheets("sheet1").Select

Selection.AutoFilter

Selection.AutoFilter Field:=2, Criteria1:=ClientName(i)

Cells.Copy

Sheets.Add

ActiveSheet.Paste

ActiveSheet.Name = ClientName(i)

Sheets("sheet1").Select
Next i
End Sub