This will get you started creating a list of distinct items, assuming you
care about case sensitivity and assuming that there is a column heading that
you want to retain. Working with the entire column is quite fast and I
recommend it rather than figuring out start and end rows for data.


Sub Macro3()
Dim i As Long, TotalRows As Long

Columns("C:C").Select
Selection.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:=xlYes, _
MatchCase:=True
TotalRows = ActiveSheet.UsedRange.Rows.Count
For i = TotalRows To 2 Step -1
If Cells(i, 1) = Cells(i - 1, 1) Then _
Cells(i, 1).EntireRow.Delete
Next i
End Sub



"Excel Grasshopper" <Excel wrote in
message ...
G'day

I am attempting to creat a robust macro which can read a single column of
data of without knowing the size of the column beforehand. One condition
of
this column is that there are columns of data on either side of it that
are
not of interest. Another feature of the column is that is contains
repetitive
numbers.

I would like to write a macro which can take the data, isolate the unique
numbers and insert them into a new column. I would then like to be able to
work with the original and new column to perform functions such as
COUNTIF.
(These goals have been accomplished using specified column sizes in the
code.)

As I've been writing the code I've been specifying the cells that are used
for gathering input, and placing output (ex. $C$1:$C$5). I would like to
make
these cell calls dynamic, so that as the macro executes, it can adjust to
match the number of source cells and unique numbers. Has anyone got any
ideas?? Thanks.