Range Selection Using Excel Macro

  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 A开发者_JAVA技巧1 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.

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

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.