Creating Macros to work with ranges of varying sizes
 

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.

Creating Macros to work with ranges of varying sizes
 

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
__________________________

Creating Macros to work with ranges of varying sizes
 

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.

Creating Macros to work with ranges of varying sizes
 

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
__________________________