0 
 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.

Improve this question
1
  • For reference to anyone else coming upon this question, usign the Macro Recorder built in to Office can make this kind of solution very easy to find yourself. Commented Jun 27, 2012 at 18:13

2 Answers 2

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

I want to select the range between A1 to A9 also C1 to C9 . But i want macro to do this without range defining like A1 and A9 because probably the range will change like A1 will change to A5 after some alterations so i want Macro to adapt and grab the numbers accordingly
@eggplant_parm thanks but that will choose the whole range including B column. I don't want to select that column.
@user768199 This will only select columns A and C.
Oh yes eggplant_parm you have changed the code :) What if i want to add the E column again till the same row for example ? A C E selected until the same row ?
@user768199 You can add another range inside the union() with the offsets set to 4 instead of 2.
|
0

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.

Improve this answer

3 Comments

I dont understand ? I copy and pasted this code to Macro Module then click run it open the classic macro run/ create / cancel window with none of the buttons were active (except Cancel)
@user768199 Copy into a worksheet code module (the code module named after the worksheet you want the code to work on), not a macro module. You don't have to explicitly run it. Just select a range on that sheet.
WOW. How can i do this in Macro ? Also if i want to add more columns like 4th and 6th column to the selection ?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.