IrishCharm
09-02-2008, 07:13 AM
:banghead:
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
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
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
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