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

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

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