Recording Keystrokes

  • Thread starter Thread starter Geoff Sale
  • Start date Start date
G

Geoff Sale

I want to write a macro that will execute a number (up to 30) of key
strokes from wherever location I select. The macros I've recorded
always appear to need a cell reference for their Selection, and will
only execute in those cells.
I want to highlight and sort selections unequal length (selected by the
End Down sequence), anywhere in a worksheet, but my macro recording
always executes in the same location, and I don't know how to use
relative rather than absolute locations in the VB code.
Thanks for any help!
Geoff
 
Geoff

When you start recording a macro via Tools>Macro>Record New Macro the "Stop
Recording" toolbar should appear.

On this toolbar is a "Relative Reference" button. Click it to record in that
mode.

Do a few things then hit the "Stop Recording" button. Look at your code and
you will see that hard-coded cells and ranges are replaced with terms like

ActiveCell.Offset(5, 4) which means move down 5 rows and to the right 4
columns from where you started.

If you don't get the "Stop Recording" toolbar, go to Tools>Customise>Toolbars
and place a check at the "Stop Recording" toolbar.

There are other methods of selecting ranges using Activecell as a start point.

Examples..........

Sub selectrange1()
''from activecell in any column to bottom of used range in column including
blanks
Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Select
End Sub

Sub SelectDown()
''to first blank row in active column
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub

Sub findbottom22()
''returns last row in active column
Lastrow = ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
MsgBox "Last Row is " & Lastrow
End Sub

Also check out VBE Help on "Resize Property" for another method of changing
the size of a selection.

Gord Dibben XL2002
 
There it was, right under my nose!
Thanks, Gord! You've saved me a heap of time!!
Geoff
 
Back
Top