Select or deselect offset entire column double clicking on cell

[motilulla]

Hello,</SPAN></SPAN>

I want a code that can select or deselect offset entire column double clicking on cell.</SPAN></SPAN>
For example if I double click on any cell in the column "L", I want entire column "C" could be selected. If I redo double click then entire column deselected</SPAN></SPAN>
For example if I double click on any cell in the column "N", I want entire column "D" could be selected. If I redo double click then entire column deselected</SPAN></SPAN>

Data looks as shown below</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2
3
4
5			n1	n2	n3	n4	n5	n6				R1		R2		R3		R4		R5		R6
6			0	1	1	1	2	1				0		1		1		1		-1		1
7			0	0	3	2	0	0				0		0		0		0		0		0
8			0	1	4	3	2	1				-3		1		1		3		-3		1
9			2	2	5	0	4	2				-4		2		2		2		-4		2
10			3	0	6	0	5	3				-3		1		3		1		-3		3
11			0	2	0	0	7	0				-4		0		2		0		-4		2
12			0	3	1	2	8	1				-5		1		3		-1		-3		3
13			1	0	0	4	9	0				-4		0		2		-2		-2		2
14			2	1	1	6	0	0				-3		1		3		-3		-3		1
15			3	0	0	7	1	1				-2		0		2		-2		-2		2
16			0	1	0	9	2	3				-3		1		1		-3		-1		1
17			1	2	0	10	4	4				-2		2		0		-2		-2		2
18			2	3	1	11	5	0				-1		3		1		-1		-1		1
19			0	4	2	0	0	1				-2		4		2		-2		-2		2
20			1	5	3	1	2	2				-1		5		3		-1		-3		3
21			2	6	4	3	4	3				0		6		4		-2		-4		4
22			4	7	0	4	5	0				-1		7		3		-1		-3		3
23			5	8	1	5	6	2				0		8		4		0		-2		2
24			0	9	2	7	7	3				-1		9		5		-1		-1		3
25			1	10	3	8	0	4				0		10		6		0		-2		4
26			0	11	4	9	1	6				-1		11		7		1		-1		3
27			1	13	0	0	0	0				0		10		6		0		-2		2
28			2	14	1	2	1	2				1		11		7		-1		-1		1
29			3	15	2	4	3	3				2		12		8		-2		-2		2
30			4	16	3	0	4	4				3		13		9		-3		-1		3
31			5	17	4	0	5	5				4		14		10		-4		0		4
32			6	0	5	1	7	6				5		13		11		-3		-1		5
33			8	2	6	2	0	7				4		12		12		-2		-2		6
34			9	0	7	4	1	8				5		11		13		-3		-1		7
35			11	1	8	5	3	0				4		12		14		-2		-2		6
36			12	0	0	7	4	1				5		11		13		-3		-1		7
Sheet1

Thanks In Advance </SPAN></SPAN>
Using version 2000</SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[James006]

Hello,

From your sample ... it looks like your Offset is not constant ...

Due to all empty columns inserted from L to V ... the offset goes 9 to 14 ...

So you will need

=INT((Column()-7)/2)+MOD((Column()-7),2)

Hope this will help

 

[motilulla]

Hello,

From your sample ... it looks like your Offset is not constant ...

Due to all empty columns inserted from L to V ... the offset goes 9 to 14 ...

So you will need

=INT((Column()-7)/2)+MOD((Column()-7),2)

Hope this will help

Hello James006, thank you for looking my request, how can I use this code line I create a sub in a module and insert this line it turns red. Please can you advice or make a sub so I can use it directly</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[James006]

Re,

As an initial step ... you could test following :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column < 12 And Target.Column > 22 Then Exit Sub
Dim j As Long
j = Target.Column
Cells(Target.Row, Int((j - 7) / 2) + (j - 7) Mod 2).Select
Cancel = True
End Sub

Hope this will help

 

[motilulla]

Re,

As an initial step ... you could test following :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column < 12 And Target.Column > 22 Then Exit Sub
Dim j As Long
j = Target.Column
Cells(Target.Row, Int((j - 7) / 2) + (j - 7) Mod 2).Select
Cancel = True
End Sub

Hope this will help

James006, I would not have been able to make it worked. Thank you for building a macro. It works but it select only the one offset cell can you make it work so it select entire column if possible</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[James006]

Hello again,

Below is the requested modification :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column < 12 And Target.Column > 22 Then Exit Sub
Dim j As Long
Dim k As Long
j = Target.Column
k = Int((j - 7) / 2) + (j - 7) Mod 2
Range(Cells(Target.Row, k), Cells(Cells(Rows.Count, k).End(xlUp).Row, k)).Select
Cancel = True
End Sub

Hope this will help

 

[Fluff]

Alternately

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column < 12 [COLOR=#ff0000]Or [/COLOR]Target.Column > 22 Then Exit Sub
Dim j As Long
Dim k As Long
j = Target.Column
k = Int((j - 7) / 2) + (j - 7) Mod 2
[COLOR=#ff0000]Columns(k).Select[/COLOR]
Cancel = True
End Sub

 

[motilulla]

Hello again,

Below is the requested modification :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column < 12 And Target.Column > 22 Then Exit Sub
Dim j As Long
Dim k As Long
j = Target.Column
k = Int((j - 7) / 2) + (j - 7) Mod 2
Range(Cells(Target.Row, k), Cells(Cells(Rows.Count, k).End(xlUp).Row, k)).Select
Cancel = True
End Sub

Hope this will help

James006, whit this modifications I see the code select Offset to last cell not the entire column. Any ways it another idea not bad </SPAN></SPAN>

Thank you for your kind help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[motilulla]

Alternately

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column < 12 [COLOR=#ff0000]Or [/COLOR]Target.Column > 22 Then Exit Sub
Dim j As Long
Dim k As Long
j = Target.Column
k = Int((j - 7) / 2) + (j - 7) Mod 2
[COLOR=#ff0000]Columns(k).Select[/COLOR]
Cancel = True
End Sub

Thank you Fluff, this is what I wanted it is selecting entire column Perfect! </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[Fluff]

Glad we could help & thanks for the feedback