In my sheet, I have a range of data like this...

Class Total
500 20.0
500 12.0
500 35.5
502 28.3
502 13.7
503 19.3
504 21.4
504 30.7
504 14.5
....

In VBA, I need to copy the classes to an array, but with no duplicate.
I mean 500, 502, 503 and 504. How can I do that? Thank you in advance.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

Something like this...
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'----------
Sub GetStarted()
Call NoEvilTwins(Selection)
End Sub
'----------

Function NoEvilTwins(ByRef rngArea As Excel.Range)
'Jim Cone - San Francisco, USA - March 07, 2006
Dim colScreener As VBA.Collection
Dim varArray() As Variant
Dim rngCell As Excel.Range
Dim lngC As Long
Dim lngR As Long

Set colScreener = New VBA.Collection
ReDim varArray(1 To rngArea.Rows.Count, 1 To rngArea.Columns.Count)
lngR = 1

For Each rngCell In rngArea.Columns(1).Cells
On Error Resume Next
colScreener.Add rngCell.Value, CStr(rngCell.Value)
If Err.Number = 0 Then
On Error GoTo 0
'Load the array
For lngC = 1 To rngArea.Columns.Count
varArray(lngR, lngC) = rngCell(1, lngC).Value
Next
lngR = lngR + 1
Else
Err.Clear
On Error GoTo 0
End If
Next

'Pick a place to put it.
'If there were duplicates then the array will have empty elements.
Range("A1").Resize(colScreener.Count, rngArea.Columns.Count).Value = varArray

Set colScreener = Nothing
End Function
'----------


"MarcL via OfficeKB.com" <u19204@uwe wrote in message
In my sheet, I have a range of data like this...
Class Total
500 20.0
500 12.0
500 35.5
502 28.3
502 13.7
503 19.3
504 21.4
504 30.7
504 14.5
In VBA, I need to copy the classes to an array, but with no duplicate.
I mean 500, 502, 503 and 504. How can I do that? Thank you in advance.

Thank you Jim. But I am astonished that there is no function in Excel that
could do that... like filter advanced combined with copytoarray function.

Jim Cone wrote:
Something like this...
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'----------
Sub GetStarted()
Call NoEvilTwins(Selection)
End Sub
'----------

Function NoEvilTwins(ByRef rngArea As Excel.Range)
'Jim Cone - San Francisco, USA - March 07, 2006
Dim colScreener As VBA.Collection
Dim varArray() As Variant
Dim rngCell As Excel.Range
Dim lngC As Long
Dim lngR As Long

Set colScreener = New VBA.Collection
ReDim varArray(1 To rngArea.Rows.Count, 1 To rngArea.Columns.Count)
lngR = 1

For Each rngCell In rngArea.Columns(1).Cells
On Error Resume Next
colScreener.Add rngCell.Value, CStr(rngCell.Value)
If Err.Number = 0 Then
On Error GoTo 0
'Load the array
For lngC = 1 To rngArea.Columns.Count
varArray(lngR, lngC) = rngCell(1, lngC).Value
Next
lngR = lngR + 1
Else
Err.Clear
On Error GoTo 0
End If
Next

'Pick a place to put it.
'If there were duplicates then the array will have empty elements.
Range("A1").Resize(colScreener.Count, rngArea.Columns.Count).Value = varArray

Set colScreener = Nothing
End Function
'----------

"MarcL via OfficeKB.com" <u19204@uwe wrote in message
In my sheet, I have a range of data like this...
Class Total
500 20.0
500 12.0
500 35.5
502 28.3
502 13.7
503 19.3
504 21.4
504 30.7
504 14.5
In VBA, I need to copy the classes to an array, but with no duplicate.
I mean 500, 502, 503 and 504. How can I do that? Thank you in advance.

--
Message posted via http://www.officekb.com

Well you can pick up any set of range values using a Variant variable.
The result is a Variant containing an array.

Dim varArray as Variant
dim dblValue as Double

varArray = Range("B10: F30").Value
dblValue = varArray(3, 4)

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"MarcL via OfficeKB.com" <u19204@uwe wrote in message news:5cf3366dd5bdf@uwe...
Thank you Jim. But I am astonished that there is no function in Excel that
could do that... like filter advanced combined with copytoarray function.

Jim Cone wrote:
Something like this...
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'----------
Sub GetStarted()
Call NoEvilTwins(Selection)
End Sub
'----------

Function NoEvilTwins(ByRef rngArea As Excel.Range)
'Jim Cone - San Francisco, USA - March 07, 2006
Dim colScreener As VBA.Collection
Dim varArray() As Variant
Dim rngCell As Excel.Range
Dim lngC As Long
Dim lngR As Long

Set colScreener = New VBA.Collection
ReDim varArray(1 To rngArea.Rows.Count, 1 To rngArea.Columns.Count)
lngR = 1

For Each rngCell In rngArea.Columns(1).Cells
On Error Resume Next
colScreener.Add rngCell.Value, CStr(rngCell.Value)
If Err.Number = 0 Then
On Error GoTo 0
'Load the array
For lngC = 1 To rngArea.Columns.Count
varArray(lngR, lngC) = rngCell(1, lngC).Value
Next
lngR = lngR + 1
Else
Err.Clear
On Error GoTo 0
End If
Next

'Pick a place to put it.
'If there were duplicates then the array will have empty elements.
Range("A1").Resize(colScreener.Count, rngArea.Columns.Count).Value = varArray

Set colScreener = Nothing
End Function
'----------

"MarcL via OfficeKB.com" <u19204@uwe wrote in message
In my sheet, I have a range of data like this...
Class Total
500 20.0
500 12.0
500 35.5
502 28.3
502 13.7
503 19.3
504 21.4
504 30.7
504 14.5
In VBA, I need to copy the classes to an array, but with no duplicate.
I mean 500, 502, 503 and 504. How can I do that? Thank you in advance.

--
Message posted via http://www.officekb.com