Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Record macro and obtain variable range results?
Am recording a macro to "clean up" imported data, and as we know, Excel
picks up firm cell locations rather than the range desired. This is undesirable, as the sheets are different each time and the ranges vary. My question is: seeing the examples of the code below, is there a way to obtain such code via the keyboard during the recording phase, without taking the results (which contain specific cell references) and reinterpreting them and adjusting each one by hand? This macro is quite lengthy. (examples taken from J-walk.com(rangesel.xls)): Sub SelectDown() Range(ActiveCell, ActiveCell.End(xlDown)).Select End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ' Sub SelectUp() Range(ActiveCell, ActiveCell.End(xlUp)).Select End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ' Sub SelectToRight() Range(ActiveCell, ActiveCell.End(xlToRight)).Select End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ' Sub SelectToLeft() Range(ActiveCell, ActiveCell.End(xlToLeft)).Select End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ' Sub SelectCurrentRegion() ActiveCell.CurrentRegion.Select End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ' Sub SelectActiveArea() Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select End Sub TIA for any ideas. Pierre |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Record macro and obtain variable range results?
Pierre
When recording, enable the Relative Reference Button then use selection shortcut keys. Sub Macro2() 'Using SHIFT + End + DownArrow with Relative Reference Button enabled Range(Selection, Selection.End(xlDown)).Select End Sub Sub Macro5() 'using SHIFT + End + RightArrow Range(Selection, Selection.End(xlToRight)).Select End Sub When moving the mouse from one cell to another you will get code like this. ActiveCell.Offset(-16, -1).Range("A1").Select Gord Dibben MS Excel MVP On 17 Aug 2006 14:10:33 -0700, "Pierre" wrote: Am recording a macro to "clean up" imported data, and as we know, Excel picks up firm cell locations rather than the range desired. This is undesirable, as the sheets are different each time and the ranges vary. My question is: seeing the examples of the code below, is there a way to obtain such code via the keyboard during the recording phase, without taking the results (which contain specific cell references) and reinterpreting them and adjusting each one by hand? This macro is quite lengthy. (examples taken from J-walk.com(rangesel.xls)): Sub SelectDown() Range(ActiveCell, ActiveCell.End(xlDown)).Select End Sub ''''''''''''''''''''''''''''''''''''''''''''''''' '' Sub SelectUp() Range(ActiveCell, ActiveCell.End(xlUp)).Select End Sub ''''''''''''''''''''''''''''''''''''''''''''''''' '' Sub SelectToRight() Range(ActiveCell, ActiveCell.End(xlToRight)).Select End Sub ''''''''''''''''''''''''''''''''''''''''''''''''' '' Sub SelectToLeft() Range(ActiveCell, ActiveCell.End(xlToLeft)).Select End Sub ''''''''''''''''''''''''''''''''''''''''''''''''' '' Sub SelectCurrentRegion() ActiveCell.CurrentRegion.Select End Sub ''''''''''''''''''''''''''''''''''''''''''''''''' '' Sub SelectActiveArea() Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select End Sub TIA for any ideas. Pierre |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Record macro and obtain variable range results?
Gord Dibben wrote: Pierre When recording, enable the Relative Reference Button then use selection shortcut keys. Gord, Thanks for the reply. Still working on obtaining a workable version; some compile errors which halts the macro. Looks as though I still need to determine when to use a relative reference, and when to use absolute. Even when using relative references, it's still indicating cell locations. This may be normal. I'll keep hammering away. Pierre |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup formulas to obtain multiple results | Excel Worksheet Functions | |||
Macro hanging up on 2nd use and later. | Excel Discussion (Misc queries) | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) |