Solved: Clear out Column when finished

[IrishCharm]

Hi,

The below code pulls out distinct values in column A and pastes them into Column B. The only problem is that it "stores" the values in VB so if i want to run it again for a different set of values in column A it runs correctly but it will also post out the original values also. not sure how to clear this out between uses, any help would be appreciated.

i tried setting objcoll to null but this does not seem to help.

Sarah

[VBA]Sub DistinctValue()
lngMaxRow = Application.Max(Cells(Rows.Count, 1).End(xlUp).Row, Cells(Rows.Count, 1).End(xlUp).Row)
On Error Resume Next
For lngRow = 2 To lngMaxRow
For lngCol = 1 To 1
With Cells(lngRow, lngCol)
If Len(.Value) > 0 Then
objColl.Add .Value, .Value
End If
End With
Next lngCol
Next lngRow
For lngRow = 1 To objColl.Count
Cells(lngRow + 1, "B").Value = objColl(lngRow)
Next lngRow

End Sub[/VBA]

[Bob Phillips]

... post out the original values also ...

what does that mean?

[IrishCharm]

Hi,

For example:

Column A contains:w,w,w,g,g,g,g,h,h,
When i run the macro it will output w,g,h into Column B which is correct.

If i then clear out columns A and B and start again from scratch with
Column A contains: s,s,s,s,k,k,k,k,k
then the macro will output s,k which is correct but it will also output the w,g which i originally input (even though values have been cleared out!!)-

somehow the macro is storing the values w,g,h in memory and i am not sure how to "clear it down" before running it a second time with a different set of data.

If i run the macro a 3rd time with a different set of data again the output will include w,g,h,s,k along with the 3rd unique data range.

Any ideas?

[Bob Phillips]

You need to initialise your collection, otherwise you are just appending to what's there

[vba]

Sub DistinctValue()
lngMaxRow = Application.Max(Cells(Rows.Count, 1).End(xlUp).Row, Cells(Rows.Count, 1).End(xlUp).Row)
Set objColl = New Collection
On Error Resume Next
For lngRow = 2 To lngMaxRow
For lngCol = 1 To 1
With Cells(lngRow, lngCol)
If Len(.Value) > 0 Then
objColl.Add .Value, .Value
End If
End With
Next lngCol
Next lngRow
For lngRow = 1 To objColl.Count
Cells(lngRow + 1, "B").Value = objColl(lngRow)
Next lngRow

End Sub
[/vba]

[IrishCharm]

that works perfectly thanks for taht i appreciate your help with this.

Kind regards and have a nice day

Sarah