Macro code for END + Arrow key movement

[skiing]

When recording a macro - the absolute cell addresses are referenced as
an end result of my using the END & UPARROW (DOWN ARROW, etc...). In
an unknown range of data I would like to use the movement keys define
my print range. My method is to go to column D - press the END + DOWN
ARROW which moves me to the last used cell in column D. Hold the
Shift key and press CTRL HOME - which moves me back to B2 (due to
frozen titles) - then I press the UP ARROW and next the LEFT ARROW KEY
( to include row 1 and column A). Then set the print range. Can
someone advise as to macro code for this procedure?

(Many different users use a copy of this excel file - often they move
to other columns, etc.. the CTRL END ( which should take you to the
last used cell in that worksheet) does not always take you to the last
cell in column D due to their moving around. The print range should
not exceed past column D and the last used row).

 

[Gord Dibben]

Sub selectrange2()
Dim rng As Range
Set rng = Range(Range("A1"), Cells(Rows.Count, "D").End(xlUp))
MsgBox rng.Address
'rng.PrintOut
End Sub

Or a two-liner

Sub selectrange2()
Range(Range("A1"), Cells(Rows.Count, "D").End(xlUp)).Select
Selection.PrintOut
End Sub


Gord Dibben MS Excel MVP