Thread
:
Creating Macros to work with ranges of varying sizes
View Single Post
#
2
Posted to microsoft.public.excel.programming
Richard Buttrey
external usenet poster
Posts: 296
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
__________________________
Reply With Quote
Richard Buttrey
View Public Profile
Find all posts by Richard Buttrey