How to select elect a range by clicking on a cell outside of that range?

[musicgold]

Hi,

I frequently need to copy a group of cells and paste it in a Word document. However before I copy the group, I need to make a small change in a cell in the group. So I have to select the range every time; it takes a few seconds and is tedious.

So is there a way so that I can click on a cell (say A1) and select a range B9:U27?

I did look at creating a dynamic range and clicking on it from the box on the top left, however I have a number of dynamic ranges and trying to find a particular range in the list is tedious.

Thanks

 

[sykes]

Hi
Do you know how to name a range?
Name the range in question, then each time you need to select it, just pick that named range off the "Name Box" (left of the formula bar).

Is this what you need?

 

[musicgold]

Named range - that's what I meant by a dynamic range. Perhaps I used the term incorrectly.

 

[sykes]

Sorry - halfway through reply......

Yes - I re-read the rest of your post......

You could use VBA for this; if placed in the worksheet module of the sheet in question, this will do what you need:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$A$1" Then Range("test15").Select
End Sub

Can you use VBA?

.........you'll need to change the "test15" to the name of your (dynamic) range.

 

[musicgold]

Thanks. Yes, I can use VBA.

I did find this solution but was not sure how much it will slow down my Excel file.

Wouldn't this routine be executed every time there is a change in the file to check if A1 is selected?

 

[sykes]

No - only when the user makes a "selection change" in that particular worksheet, of your workbook so it shouldn't hamper the speed.

Just give it a go; if you don't like it, just remove the code again.

Remember - it needs to go into the worksheet's code module; quickest way to get there, is right-click that worksheet's name tab, and select "view code." Then select "Worksheet" from the LH top dropdown, and "SelectionChange" from the RH one.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

.......should appear, then just paste in the line of code:

If Target.Address = "$A$1" Then Range("test15").Select

...........and substitute "test15" with your named range......

 

[Fluff]

Another option is

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$A$1" Then
   Range("test15").Select
   Cancel = True
End If
End Sub

This will only work when you double click cell A1

 

[musicgold]

Another option is

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$A$1" Then
   Range("test15").Select
   Cancel = True
End If
End Sub

This will only work when you double click cell A1

This looks promising. Just a question, shouldn't this be After a double click?

 

[Fluff]

It can be either

 

[musicgold]

The following code doesn't seem to be working. Nothing happens when I double click Cell B4.
The code is located in the Thisworkbook code area.

Not sure what I am missing.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If Target.Address = "B4" Then
   Range("B6:U27").Select
   Cancel = True
End If
End Sub