Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Macros to work with ranges of varying sizes
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a chart with varying data value ranges | Excel Discussion (Misc queries) | |||
Pallets w/varying sizes & weights need to fit in restricted space | Excel Discussion (Misc queries) | |||
can th same spreadsheet have varying cell sizes top & bottom | Excel Discussion (Misc queries) | |||
Calculation for varying ranges | Excel Worksheet Functions | |||
source data of varying sizes | Charts and Charting in Excel |