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

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" <cowguy@aol.com> 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

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