I like Rich's answer and the VBA code equivalent is shown below, however it
requires the CopyTo location to be on the same worksheet which means ya
better know what your next free column is. The below assumes you will be in
the column with the repetitive data, inserts a column to the left, and
pastes unique filtered info in the new column.


ActiveCell.EntireColumn.Insert Shift:=xlToRight

With ActiveCell.Offset(0, 1).EntireColumn
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Cells(1).Offset(0, -1), _
Unique:=True
End With


"Richard Buttrey" wrote in
message ...
On Thu, 25 Aug 2005 15:29:02 -0700, "Excel Grasshopper" <Excel
wrote:

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.

Are you using Range Names, particularly for the output cells? If not I
suggest you name $C$1 with a suitable name, "MyOutput" say, and code
this in the macro instead of $C$1:$C$5. If you use the "Advanced
Filter copy to another location unique values" functionality, you only
need to specify a single cell (C1) and give this a field heading value
which matches the field heading of your single column of input data.

For the input data column itself, (starting at say B1), your macro
could identify the range of data with

Range(Range("B1"),Range("B65536").End(xlUp))

HTH


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________