Range Selection Using Excel Macro

Question

  A      B        C

123455  XXX     99999
123456  XX      100000
123457  XXX     100001
174564  XXXX    100002
184567          100003
194570          100004
204573          100005
214576          100006
224579          100007
                100008

I would like to write a macro for selecting a range using <ctrl> + <down arrow>

The process should be like this

1. Select cell A1
2. Select a Range with <shift> + <ctrl> + <page down>
3. Aelect cell A1 without cancelling the range selection (using <ctrl>)
4. ActiveCell.Offset(0, 2).Select
5. Then range select from C1 to C9 with <shift> + <ctrl> + <page down> then <shift> + <up arrow>

Following those steps in my example data, 224579 and 100007 are selected at same row, 100008 is not selected.

I want to select the range between A1 to A9 also C1 to C9, but I want the macro to do this without defining a range like A1 and A9, because the range will probably change like A1 will change to A5 after some alterations. So, I want the macro to adapt and grab the numbers accordingly.

Answer 1

This should work if A1 is the active cell.

Union(Range(ActiveCell, ActiveCell.End(xlDown)), Range(ActiveCell.Offset(0, 2), ActiveCell.End(xlDown).Offset(0, 2))).Select

Answer 2

Not sure, but do you want this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Static self_protect As Boolean

  If self_protect Then Exit Sub

  self_protect = True
  Set Target = Target.Areas(1)
  Application.Union(Target, Target.Offset(0, 2)).Select
  self_protect = False
End Sub

Paste the code into a worksheet code module and select a range on the sheet.